package dwbrowser;

//import java.io.FileInputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
//import java.io.OutputStream;
import java.io.PrintWriter;
import java.net.URLDecoder;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.json.JSONException;
import org.json.JSONObject;

import sun.misc.CharacterEncoder;

import java.sql.Connection;
//import java.sql.Date;
//import java.sql.Timestamp;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.text.*;
//import java.util.Calendar;
//import java.util.Date;
import java.util.HashMap;
import java.util.Locale;
import java.util.UUID;
/*
import java.awt.Component;
import java.awt.EventQueue;
import java.awt.Graphics;
import java.awt.event.*;
import java.io.*;
import javax.swing.*;*/
import java.util.Map;
import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.TimeUnit;

public class Dwbrowser extends HttpServlet 
{
	private static final long serialVersionUID = 1L;
	//URLָ��Ҫ���ʵ���ݿ���
//	private static final String URL="jdbc:mysql://localhost:3306/dwaccessDB";
	private static final String URL="jdbc:mysql://localhost:3306/dwjava";
	//MySQL����ʱ���û���
    private static final String NAME="root";
  //MySQL����ʱ������
	private static final String PASSWORD="root";
	/**
	 * Constructor of the object.
	 */
	public Dwbrowser() 
	{
		super();
	}

	/**
	 * Destruction of the servlet. <br>
	 */
	public void destroy() 
	{
		super.destroy(); // Just puts "destroy" string in log
		// Put your code here
	}

	/**
	 * The doGet method of the servlet. <br>
	 *
	 * This method is called when a form has its tag value method equals to get.
	 * 
	 * @param request the request send by the client to the server
	 * @param response the response send by the server to the client
	 * @throws ServletException if an error occurred
	 * @throws IOException if an error occurred
	 */
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException 
	{
/*		String content = null;
		char[] buf = new char[512];  
        int len = 0;  
        StringBuffer contentBuffer = new StringBuffer(); 
        while ((len = request.getReader().read(buf)) != -1) 
        {  
            contentBuffer.append(buf, 0, len);  
        } 
        content = contentBuffer.toString();
//        System.out.println("doGet: "+ content);*/
		String picfilename = request.getParameter("picfilename");

//		String Path = request.getSession().getServletContext().getRealPath("/");
//		String Path = "/code/server/java/dwserver";
/*		String Path = "D:/apache-tomcat-7/webapps/dw/pics/";
		FileInputStream fileIs=null;
	    try 
	    {
	    	fileIs = new FileInputStream(Path+picfilename);
	    } catch (Exception e) 
	    {
	    	System.out.println("ϵͳ�Ҳ���ͼ���ļ���"+Path+picfilename);        
	    	return;
	    }
	    int i=fileIs.available(); //�õ��ļ���С   
        byte data[]=new byte[i];   
        fileIs.read(data);  //�����   
        response.setContentType("text/html; charset=UTF-8");
        response.setContentType("image/*"); //���÷��ص��ļ����� 
        OutputStream outStream=response.getOutputStream(); //�õ���ͻ��������������ݵĶ���   
        outStream.write(data);  //������      
        outStream.flush();  
        outStream.close();   
        fileIs.close(); */
//		String th = "    This is ";
		if(!picfilename.isEmpty())
		{
			response.setContentType("text/html");
			PrintWriter out = response.getWriter();
			out.println("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">");
			out.println("<HTML>");
			out.println("  <HEAD><TITLE>A Servlet</TITLE></HEAD>");
			out.println("  <BODY>");
	/*		out.print("    This is ");
			out.print(th);
			out.print(this.getClass());
			out.println(", using the GET method");*/
			out.println("  <img src=\"/dw/pics/");
	//		out.println(picfilename);
			out.print(picfilename);
			out.println(  "\">");
			out.println("  </BODY>");
			out.println("</HTML>");
			out.flush();
			out.close();
		}
//		String table = request.getParameter("table");
//		System.out.println("table:"+table);		
	}

	/**
	 * The doPost method of the servlet. <br>
	 *
	 * This method is called when a form has its tag value method equals to post.
	 * 
	 * @param request the request send by the client to the server
	 * @param response the response send by the server to the client
	 * @throws ServletException if an error occurred
	 * @throws IOException if an error occurred
	 */
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException 
	{
		String content = null;
		char[] buf = new char[512];  
        int len = 0;  
        StringBuffer contentBuffer = new StringBuffer(); 
        while ((len = request.getReader().read(buf)) != -1) 
        {  
            contentBuffer.append(buf, 0, len);  
        }
        content = contentBuffer.toString();
//        System.out.println("doPost: "+ content);
        
        if(content.indexOf("dwsignatureCheck")>-1)
        {
        	try 
        	{
				dwinitdb(response);
				dwtimertask();
				dwupdatedevice(content,response);
			} catch (SQLException | JSONException e) 
			{
				// TODO Auto-generated catch block
//				e.printStackTrace();
				content="{\"res\":\"deny5\"}";
		    	sendReturnstr(content,response);
			}        	
        }
        
        if(content.indexOf("dwcontrlerpage")!=-1)
        {
//        	System.out.println("out2: "+ content);
//        	String dwcontrlerpage = request.getParameter("dwcontrlerpage");
//        	System.out.println("dwaccesspage_1: "+ dwaccesspage);
        	JSONObject json = null;
        	String dwcontrlerpage = "";
        	try 
        	{
				json = new JSONObject(content);
			} catch (JSONException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        	try 
        	{
        		dwcontrlerpage=json.getString("dwcontrlerpage");
			} catch (JSONException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
//        	System.out.println("dwcontrlerpage_2: "+ dwcontrlerpage);
        	try 
        	{
        		dwgetcontroler(response,Integer.parseInt(dwcontrlerpage));
			} catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        }
        
        if(content.indexOf("dwaccesspage")!=-1)
        {
//        	System.out.println("dwaccesspage: "+ content);
//        	String dwaccesspage = request.getParameter("dwaccesspage");
//        	System.out.println("dwaccesspage_1: "+ dwaccesspage);
        	JSONObject json = null;
        	String dwaccesspage = "";
        	try 
        	{
				json = new JSONObject(content);
			} catch (JSONException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        	try 
        	{
				dwaccesspage=json.getString("dwaccesspage");
			} catch (JSONException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
//        	System.out.println("dwaccesspage_2: "+ dwaccesspage);
        	try 
        	{
				dwaccessrecord(response,Integer.parseInt(dwaccesspage));
			} catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        }
        
        if(content.matches("dwgetcontroler"))
        {
//        	System.out.println("dwgetcontroler: "+ content);
        	try 
        	{
        		dwgetcontroler(response,1);
			} catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        }
        
        if(content.matches("dwaccessrecord"))
        {
//        	System.out.println("dwaccessrecord: "+ content);
        	try 
        	{
				dwaccessrecord(response,1);
			} catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        }
        
        if(content.matches("dwviewaccessdetail"))
        {
//        	System.out.println("conten_469: "+content);
        	try 
        	{
				dwviewaccessdetail(response);
			} catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        }
        
        if(content.indexOf("table")!=-1)
        {
//        	System.out.println("dwaccesspage: "+ content);
//        	String table = request.getParameter("table");
//        	System.out.println("dwaccesspage_1: "+ dwaccesspage);
        	JSONObject json = null;
        	String table = ""; 
        	try 
        	{
				json = new JSONObject(content);
			} catch (JSONException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        	try 
        	{
        		table=json.getString("table");
			} catch (JSONException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
//        	System.out.println("table_2: "+ table);
        	try 
        	{
				exceltable(response,table);
			} catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        }
        
        if(content.indexOf("dwgetControlerNum")!=-1)
		{
//        	System.out.println("dwgetControlerNum: "+ content);
        	try 
        	{
        		dwgetControlerNum(response);
			} catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}       	
		}
        
/*		response.setContentType("text/html");
		PrintWriter out = response.getWriter();
		out.println("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">");
		out.println("<HTML>");
		out.println("  <HEAD><TITLE>A Servlet</TITLE></HEAD>");
		out.println("  <BODY>");
		out.print("    This is ");
		out.print(this.getClass());
		out.println(", using the POST method");
		out.println("  </BODY>");
		out.println("</HTML>");
		out.flush();
		out.close();*/
        if(content.indexOf("dwgetstaff")!=-1)
		{
//        	System.out.println("dwgetstaff: "+ content);
        	JSONObject json = null;
        	String stffpagenum = ""; 
        	try 
        	{
				json = new JSONObject(content);
			} catch (JSONException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        	try 
        	{
        		stffpagenum=json.getString("stffpagenum");
			} catch (JSONException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        	try 
        	{
				dwgetstaff(response,Integer.parseInt(stffpagenum));
			} catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}       	
		}
        
//        String controlerNum = null;
//        String staffnum = null;
//        String controlerNum = new String();
 //       String staffnum = new String();
//        @SuppressWarnings("rawtypes")
		Map <String,String>map = new HashMap<String,String>();
        if(content.indexOf("dwaddstaff")!=-1)
		{
//        	System.out.println("dwaddstaff: "+ content);
        	String[] strbuf = content.split("&");
        	for(String strings:strbuf)
        	{
        		if(strings != null && strings.trim().length() != 0)
        		{
	//        		System.out.println("strbuf[0]: "+strbuf[0]);
//	        		System.out.println("strbuf[1]: "+strbuf[1]);
//	        		System.out.println("strings: "+strings);
	        		String[] x = strings.split("=");
	        		if(x != null && x.length == 2)
	        		{
//		        		System.out.println("x[0]: "+x[0]);
//		        		System.out.println("x[1]: "+x[1]);
//	        			map.put(x[0], x[1]);
	        			map.put(x[0], URLDecoder.decode(x[1],"utf-8"));
//		        		if(x[0]=="controlerNum")
//		        		{
//		        			contentBuffer.append(x[1], 0, x[1].length());
//		        			contentBuffer.append(x[1]);
//		        			controlerNum = contentBuffer.toString();
//		        			controlerNum = x[1];
//		        		}
//		        		controlerNum.concat(x[1]);
//		        		if(x[0]=="staffnum") staffnum.concat(x[1]);
	        		}
        		}
        	}
        	/*         	String controlerNum = (String)map.get("controlerNum");
        	String staffnum =  (String)map.get("staffnum");
        	String staffname = (String)map.get("staffname");
//        	String staffname = new String(staffname1.getBytes("iso-8859-1"), "gb2312");
//        	String staffname = new String(staffname1.getBytes("iso-8859-1"), "utf-8");
        	String cardnum = (String)map.get("cardnum");
        	String departmentname =  (String)map.get("departmentname");
        	String departmentID = (String)map.get("departmentID");
        	String gateName = (String)map.get("gateName");
        	String periodofvalitystart =  (String)map.get("periodofvalitystart");
        	String periodofvalityend = (String)map.get("periodofvalityend");
        	String entrancevalitystart = (String)map.get("entrancevalitystart");
        	String entrancevalityend =  (String)map.get("entrancevalityend");
        	String leavevalitystart = (String)map.get("leavevalitystart");
        	String leavevalityend = (String)map.get("leavevalityend");
        	String staffgender = (String)map.get("staffgender");
        	String staffage =  (String)map.get("staffage");
        	String picfilename = (String)map.get("picfilename");
       	String controlerNum = URLDecoder.decode((String)map.get("controlerNum"),"utf-8");
        	String staffnum =  URLDecoder.decode((String)map.get("staffnum"),"utf-8");
        	String staffname = URLDecoder.decode((String)map.get("staffname"),"utf-8");
//        	String staffname = new String(staffname1.getBytes("iso-8859-1"), "gb2312");
//        	String staffname = new String(staffname1.getBytes("iso-8859-1"), "utf-8");
        	String cardnum = URLDecoder.decode((String)map.get("cardnum"),"utf-8");
        	String departmentname =  URLDecoder.decode((String)map.get("departmentname"),"utf-8");
        	String departmentID = URLDecoder.decode((String)map.get("departmentID"),"utf-8");
        	String gateName = URLDecoder.decode((String)map.get("gateName"),"utf-8");
        	String periodofvalitystart =  URLDecoder.decode((String)map.get("periodofvalitystart"),"utf-8");
        	String periodofvalityend = URLDecoder.decode((String)map.get("periodofvalityend"),"utf-8");
        	String entrancevalitystart = URLDecoder.decode((String)map.get("entrancevalitystart"),"utf-8");
        	String entrancevalityend =  URLDecoder.decode((String)map.get("entrancevalityend"),"utf-8");
        	String leavevalitystart = URLDecoder.decode((String)map.get("leavevalitystart"),"utf-8");
        	String leavevalityend = URLDecoder.decode((String)map.get("leavevalityend"),"utf-8");
        	String staffgender = URLDecoder.decode((String)map.get("staffgender"),"utf-8");
        	String staffage =  URLDecoder.decode((String)map.get("staffage"),"utf-8");
        	String picfilename = (String)map.get("picfilename");
//        	String urlstr = request.getRequestURI();
//        	String urlstr = request.getRequestURL().toString();
//        	System.out.println("urlstr: "+ urlstr);
//        	String controlerNum = request.getParameter("controlerNum");
//        	String staffnum = request.getParameter("staffnum");
/*        	String staffname = request.getParameter("staffname");
        	String cardnum = request.getParameter("cardnum");
        	String departmentname = request.getParameter("departmentname");
        	String departmentID = request.getParameter("departmentID");
        	String gateName = request.getParameter("gateName");
        	String periodofvalitystart = request.getParameter("periodofvalitystart");
        	String periodofvalityend = request.getParameter("periodofvalityend");
        	String entrancevalitystart = request.getParameter("entrancevalitystart");
        	String entrancevalityend = request.getParameter("entrancevalityend");
        	String leavevalitystart = request.getParameter("leavevalitystart");
        	String leavevalityen = request.getParameter("leavevalityen");
        	String staffgender = request.getParameter("staffgender");
        	String staffage = request.getParameter("staffage");
        	String picfilename = request.getParameter("picfilename");
        	System.out.println("controlerNum: "+ controlerNum+
        			           " staffnum: "+staffnum+
        			           " staffname: "+staffname+
        			           " cardnum: "+cardnum+
        			           " departmentname: "+departmentname+
        			           " departmentID: "+departmentID+
        			           " gateName: "+gateName+
        			           " periodofvalitystart: "+periodofvalitystart+
        			           " periodofvalityend: "+periodofvalityend+
        			           " entrancevalitystart: "+entrancevalitystart+
        			           " entrancevalityend: "+entrancevalityend+
        			           " leavevalitystart: "+leavevalitystart+
        			           " leavevalityend: "+leavevalityend+
        			           " staffgender: "+staffgender+
        			           " staffage: "+staffage+
        			           " picfilename: "+picfilename);*/
/*        	JSONObject json = null;
        	String stffpagenum = ""; 
        	try 
        	{
				json = new JSONObject(content);
			} catch (JSONException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        	try 
        	{
        		stffpagenum=json.getString("stffpagenum");
			} catch (JSONException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}*/ 
        	try 
        	{
        		dwaddstaff(response,map);
			} catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}      	
		}
        
        if(content.indexOf("dwdelstaff")!=-1)
		{
        	System.out.println("dwdelstaff: "+ content);
        	JSONObject json = null;
        	try 
        	{
				json = new JSONObject(content);
			} catch (JSONException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        	String delstr = null;
			try 
			{
				delstr = json.getString("delarr");
			} catch (JSONException e1) 
			{
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
        	String[] delarr = delstr.split(",");
        	for(String s:delarr)
        	{
        		if(s != null && s.trim().length() != 0)
        		{        			
        			try 
        			{
						dwdelstaff(response,Integer.parseInt(s));
					} catch (NumberFormatException | SQLException e) 
					{
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
        		}
        	}
		}
        
        if(content.indexOf("dwviewstaff")!=-1)
		{
//        	System.out.println("dwviewstaff: "+ content);
        	JSONObject json = null;
        	try 
        	{
				json = new JSONObject(content);
			} catch (JSONException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        	String skid = null;
			try 
			{
				skid = json.getString("skid");
			} catch (JSONException e1) 
			{
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
       			
			try 
			{
				dwviewstaff(response,Integer.parseInt(skid));
			} catch (NumberFormatException | SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
        
        if(content.indexOf("dwgetservertab")!=-1)
		{
//        	System.out.println("dwgetservertab: "+ content);               			
			try 
			{
				dwgetservertab(response);
			} catch (NumberFormatException | SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
        
        if(content.indexOf("dwdownserver")!=-1)
		{
//        	System.out.println("dwgetservertab: "+ content);
        	
			try 
			{
				dwdownserver(response,content);
			} catch (NumberFormatException | SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
        
        if(content.indexOf("dwdowngate")!=-1)
		{
//        	System.out.println("dwgetservertab: "+ content);
        	
			try 
			{
				dwdowngate(response,content);
			} catch (NumberFormatException | SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
        
        if(content.indexOf("dwgetgatetab")!=-1)
		{
//        	System.out.println("dwgetgatetab: "+ content);               			
			try 
			{
				dwgetgatetab(response,content);
			} catch (NumberFormatException | SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (JSONException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
        
	}
	
	private void dwupdatedevice(String content, HttpServletResponse response) throws IOException, JSONException, SQLException
	{
	    if(content.isEmpty())
	    {  
	    	content="{\"res\":\"deny5\"}";
	    	sendReturnstr(content,response);
	    } 
	//    System.out.println("out1: "+ content); 
	    JSONObject jsonO = null;
		jsonO = new JSONObject(content);
		content=jsonO.getString("dwsignatureCheck");
	    sun.misc.BASE64Decoder decoder = new sun.misc.BASE64Decoder();
	    byte[] bt = null;

		bt = decoder.decodeBuffer(content);

	    content=new String(bt);
	//    System.out.println("out2: "+ content);
	    jsonO = new JSONObject(content);
	//    JSONObject jsonObject2 = new JSONObject("{'Name':'Tom','age':'11'}"); 
	//    JSONObject json = JSONObject.fromObject(content);
	//	JSONArray jsonArray = JSONArray.fromObject(content);
	//	JSONTokener jsonTokener = new JSONTokener(content); 
	    
	//	JSONObject jsonO;
	//	jsonO = (JSONObject)jsonTokener.nextValue(); 
	    String username =null;
//	    try 
//	    {
    	username=jsonO.getString("username");
    	if(username.isEmpty())
    	{
    		content="{\"res\":\"deny3\"}";
        	sendReturnstr(content,response);
    	}else if(username.length()>24)
		{
			content="{\"res\":\"deny6\"}";
			sendReturnstr(content,response);
		}
//	    }
/*	    catch (JSONException e) 
		{
	    	content="{\"res\":\"deny5\"}";
	    	sendReturnstr(content,response);
		}*/
	    String password =null;
//	    try 
//	    {
    	password= jsonO.getString("password");
    	if(password.isEmpty())
		{
		   	content="{\"res\":\"deny4\"}";
			sendReturnstr(content,response);		
		}else if(password.length()>48)
		{
			content="{\"res\":\"deny6\"}";
			sendReturnstr(content,response);
		}
/*		} 
	    catch (JSONException e1) {
			// TODO Auto-generated catch block
	//		e1.printStackTrace();
			content="{\"res\":\"deny5\"}";
	    	sendReturnstr(content,response);
		}*/
	    
	//	System.out.println("username : " + username+"   password : " + password);
	    try 
		{
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) 
		{
			content="{\"res\":\"deny5\"}";
        	sendReturnstr(content,response);
        	return;
		}
		//声明Connection对象
		Connection conn = null;
//		try 
//		{
		conn = DriverManager.getConnection(URL, NAME, PASSWORD);
			/*		} 
		catch (SQLException e1) 
		{
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}*/
		Statement stmt = null;
//		try 
//		{
		stmt = conn.createStatement();
/*		} catch (SQLException e1) 
		{
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}*/
		ResultSet rs = null;
//		try 
//		{
		content="select distinct staffname,signature from staffbasesetting where staffname=\""+username+"\";";
		rs = stmt.executeQuery(content);
/*		} catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}//Ñ¡Ôñimport java.sql.ResultSet;
		try 
		{*/
		while(rs.next())
		{//Èç¹û¶ÔÏóÖÐÓÐÊý¾Ý£¬¾Í»áÑ­»·´òÓ¡³öÀ´
//			System.out.println(rs.getString("staffname")+","+rs.getInt("cardnum"));
			String unamestr = rs.getString("staffname");
			String pstr = rs.getString("signature");
//			System.out.println(unamestr+","+pstr);
			if(unamestr.isEmpty())
			{
				content="{\"res\":\"deny1\"}";
	        	sendReturnstr(content,response);
			}else if(!pstr.matches(password))
			{
				content="{\"res\":\"deny2\"}";
	        	sendReturnstr(content,response);
			}else if(pstr.matches(password) && unamestr.matches(username))
			{
				content="{\"res\":\"success\"}";
	        	sendReturnstr(content,response);
			}
		}
		if(rs!=null) rs.close();
		if(stmt !=null) stmt.close();
		if(conn!=null) conn.close();
/*		} catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}*/
	}
	
	private void dwinitdb(HttpServletResponse response) throws SQLException
	{
		String content = null;
		//驱动程序名
		try 
		{
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) 
		{
			content="{\"res\":\"deny5\"}";
        	sendReturnstr(content,response);
        	return;
		}
		//声明Connection对象
		Connection conn = null;
//		try 
//		{
			//1.getConnection()方法，连接MySQL数据库
		conn = DriverManager.getConnection(URL, NAME, PASSWORD);
/*		} catch (SQLException e) 
		{
			// TODO Auto-generated catch block
//						e.printStackTrace();
			content="{\"res\":\"deny5\"}";
        	sendReturnstr(content,response);
        	return;
		}*/
		//2.创建statement类对象，用来执行SQL语句
		Statement stmt = null;
//		try 
//		{
		stmt = conn.createStatement();
/*		} catch (SQLException e) 
		{
			// TODO Auto-generated catch block
//						e.printStackTrace();
			content="{\"res\":\"deny5\"}";
        	sendReturnstr(content,response);
		}*/
		//3.ResultSet类，用来存放获取的结果集
		ResultSet rs = null;
		int udresult  = 0;
//		try 
//		{
		Statement stmt1 = null;
//		try 
//		{
		stmt1 = conn.createStatement();
/*		} catch (SQLException e) 
		{
			// TODO Auto-generated catch block
//							e.printStackTrace();
			content="{\"res\":\"deny5\"}";
        	sendReturnstr(content,response);
		}*/
		content="CREATE TABLE if not exists accessrecord(tkid integer PRIMARY KEY AUTO_INCREMENT," +
			"cardnum text, cardlen int default 26,controlerNum text,gatenum int default 0,entrance text," +
			"accessstat text,CreatedTime text);";
//			rs = stmt.executeQuery(content);
//			rs.close();
		udresult  = stmt1.executeUpdate(content);
		if(stmt1!=null) stmt1.close();
/*		} catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}//Ñ¡Ôñimport java.sql.ResultSet;
		try 
		{*/
		stmt1 = null;
//		try 
//		{
		stmt1 = conn.createStatement();
/*		} catch (SQLException e) 
		{
			// TODO Auto-generated catch block
//							e.printStackTrace();
			content="{\"res\":\"deny5\"}";
        	sendReturnstr(content,response);
		}*/
		content="create table if not exists gatesetting(controlerNum text,IP text,PORT int,wIP text," +
			"wPORT int,lasttime text,gatenum text,gatename text,controlmethod text,delayopentime text," +
			"delayclosetime text,sensorstat text,trans_id text,readerfunction text,attendancecheck text," +
			"region text,password text,forcepass text,stat text,antipassback text);";
//			rs = stmt.executeQuery(content);
//			rs.close();
		udresult  = stmt1.executeUpdate(content);
		if(stmt1!=null) stmt1.close();
/*		} catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try 
		{*/
		stmt1 = null;
//			try 
//			{
		stmt1 = conn.createStatement();
/*			} catch (SQLException e) 
			{
				// TODO Auto-generated catch block
//							e.printStackTrace();
				content="{\"res\":\"deny5\"}";
	        	sendReturnstr(content,response);
			}*/
		content="create table if not exists staffbasesetting(skid integer PRIMARY KEY AUTO_INCREMENT," +
			"staffnum text,staffname text,signature text,cardnum text,departmentname text," +
			"controlerNum text,gatenum text,periodofvalitystart text ,periodofvalityend text ," +
			"entrancevalitystart text,entrancevalityend text,limitenrancenum text,leavevalitystart text," +
			"leavevalityend text,limitleavenum text,staffgender text,staffage int default 0," +
			"picfilename text,synstate int DEFAULT 0);";
//			rs = stmt.executeQuery(content);
//			rs.close();
		udresult  = stmt1.executeUpdate(content);
		if(stmt1!=null) stmt1.close();
/*		} catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try 
	{*/
		stmt1 = null;
//			try 
//			{
		stmt1 = conn.createStatement();
/*			} catch (SQLException e) 
			{
				// TODO Auto-generated catch block
//							e.printStackTrace();
				content="{\"res\":\"deny5\"}";
	        	sendReturnstr(content,response);
			}*/
		content="create table if not exists cmdform(skid integer PRIMARY KEY AUTO_INCREMENT," +
				"controlerNum text,transid text,cmdstr text);";
//			rs = stmt.executeQuery(content);
//			rs.close();
		udresult  = stmt1.executeUpdate(content);
		if(stmt1!=null) stmt1.close();
/*		} catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try 
		{*/
		stmt1 = null;
//			try 
//			{
		stmt1 = conn.createStatement();
/*			} catch (SQLException e) 
			{
				// TODO Auto-generated catch block
//							e.printStackTrace();
				content="{\"res\":\"deny5\"}";
	        	sendReturnstr(content,response);
			}*/
		content="create table if not exists cmdform(skid integer PRIMARY KEY AUTO_INCREMENT," +
				"controlerNum text,transid text,cmdstr text);";
//			rs = stmt.executeQuery(content);
//			rs.close();
		udresult  = stmt1.executeUpdate(content);
		if(stmt1!=null) stmt1.close();
/*		} catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try 
		{*/
		stmt1 = null;
//			try 
//			{
		stmt1 = conn.createStatement();
/*			} catch (SQLException e) 
			{
				// TODO Auto-generated catch block
//							e.printStackTrace();
				content="{\"res\":\"deny5\"}";
	        	sendReturnstr(content,response);
			}*/
		content="create table if not exists serversetting(serverIP text,serverPORT int DEFAULT 80," +
			"cmdresultpath text,realtimepath text,DetectDevicepath text,Camera0ADDR text," +
			"Camera0PORT int DEFAULT 80,Camera1ADDR text,Camera1PORT int DEFAULT 80,Camera2ADDR text," +
			"Camera2PORT int DEFAULT 80,Camera3ADDR text,Camera3PORT int DEFAULT 80," +
			"reQusttime int DEFAULT 15,sVersion text);";
//			rs = stmt.executeQuery(content);
//			rs.close();
		udresult  = stmt1.executeUpdate(content);
		if(stmt1!=null) stmt1.close();
/*		} catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try 
		{*/
		content="select COUNT(*) from staffbasesetting;";
		rs = stmt.executeQuery(content);			
//			rs.close();
/*		} catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try 
		{*/
		while(rs.next())
		{
			int count = rs.getInt(1);
			if(count==0)
			{
				stmt1 = null;
				try 
				{
					stmt1 = conn.createStatement();
				} catch (SQLException e) 
				{
					// TODO Auto-generated catch block
//									e.printStackTrace();
					content="{\"res\":\"deny5\"}";
		        	sendReturnstr(content,response);
				}
				content="insert into staffbasesetting(staffnum,staffname,signature,cardnum," +
					"departmentname,controlerNum,gatenum,periodofvalitystart,periodofvalityend) " +
					"values('1','admin','e10adc3949ba59abbe56e057f20f883e','1','hr',0,'all'," +
					"'2016-01-01 00:00:00','2026-01-01 23:59:59');";
//					ResultSet rs1  = stmt.executeQuery(content);			
//					rs1.close();
				udresult  = stmt1.executeUpdate(content);
				if(stmt1!=null) stmt1.close();
			}
		}
		rs.close();
/*		} catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try 
		{*/
		content="select COUNT(*) from serversetting;";
		rs = stmt.executeQuery(content);			
//			rs.close();
/*		} catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try 
		{*/
		while(rs.next())
		{
			int count = rs.getInt(1);
			if(count==0)
			{
				stmt1 = null;
//				try 
//				{
					stmt1 = conn.createStatement();
/*				} catch (SQLException e) 
				{
					// TODO Auto-generated catch block
//									e.printStackTrace();
					content="{\"res\":\"deny5\"}";
		        	sendReturnstr(content,response);
				}*/
				content="INSERT INTO serversetting(serverIP,serverPORT,cmdresultpath,realtimepath," +
					"DetectDevicepath,Camera0ADDR,Camera0PORT,Camera1ADDR,Camera1PORT,Camera2ADDR," +
					"Camera2PORT,Camera3ADDR,Camera3PORT,reQusttime,sVersion) VALUES('10.10.10.239'," +
					"'80','/dw/Dwcmd','/dw/Dwrealtime','/dw/Dwdetectdevice','10.10.10.143','80'," +
					"'10.10.10.143','80','10.10.10.143','80','10.10.10.143','80','15','2017V08.09R');";
				udresult  = stmt1.executeUpdate(content);
				if(stmt1!=null) stmt1.close();
//					rs1.close();
			}
		}
		rs.close();
/*		} catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try 
		{*/
		stmt1 = null;
//			try 
//			{
		stmt1 = conn.createStatement();
/*			} catch (SQLException e) 
			{
				// TODO Auto-generated catch block
//							e.printStackTrace();
				content="{\"res\":\"deny5\"}";
	        	sendReturnstr(content,response);
			}*/
		content="UPDATE serversetting SET sVersion = '2017V12.26R';";
//			rs = stmt.executeUpdate(content);			
//			rs.close();
		udresult  = stmt1.executeUpdate(content);
		if(stmt1!=null) stmt1.close();
/*		} catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try 
		{*/
			content="SELECT COUNT(*) FROM gatesetting;";
			rs = stmt.executeQuery(content);			
//			rs.close();
/*		} catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try 
		{*/
		while(rs.next())
		{
			int count = rs.getInt(1);
			if(count==0)
			{
				for(int i=0;i<4;i++)
				{
					stmt1 = null;
//						try 
//						{
					stmt1 = conn.createStatement();
/*						} catch (SQLException e) 
						{
							// TODO Auto-generated catch block
//										e.printStackTrace();
							content="{\"res\":\"deny5\"}";
				        	sendReturnstr(content,response);
						}*/
					content="INSERT INTO gatesetting(controlerNum,gatenum,gatename,region,controlmethod," +
						"delayopentime,delayclosetime,sensorstat,readerfunction,attendancecheck,password," +
						"forcepass,antipassback) VALUES('ALL','" +i+"','大门','总部','card','0','0','NG'," +
						"'26','yes','123456','999999','no');";
//						ResultSet rs1  = stmt.executeQuery(content);			
//						rs1.close();
					udresult  = stmt1.executeUpdate(content);
					if(stmt1!=null) stmt1.close();
				}
			}
		}
		rs.close();
/*		} catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try 
		{*/
		content="SELECT TRIGGER_NAME FROM information_schema.triggers WHERE TRIGGER_NAME='dwTRGstaffjava';";
		rs = stmt.executeQuery(content);
//		System.out.println("SELECT TRIGGER:"+content);
//			rs.close();
/*		} catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try 
		{*/
		if(!rs.last())
		{
//		while(rs.next())
//		{
//			String tname = rs.getString(1);
//			System.out.println(" tname:"+ tname);
	//		if(tname.isEmpty())
//			if(tname==null||tname=="")
//			{
				stmt1 = null;
//					try 
//					{
				stmt1 = conn.createStatement();
/*					} catch (SQLException e) 
					{
						// TODO Auto-generated catch block
//									e.printStackTrace();
						content="{\"res\":\"deny5\"}";
			        	sendReturnstr(content,response);
					}*/
				content="CREATE TRIGGER dwTRGstaffjava AFTER INSERT ON staffbasesetting FOR EACH ROW " +
					"BEGIN " +
					"IF(NEW.cardnum=NULL OR NEW.staffnum=NULL OR NEW.departmentname=NULL OR " +
					"NEW.staffname=NULL OR NEW.gatenum=NULL OR NEW.periodofvalitystart=NULL OR " +
					"NEW.periodofvalityend=NULL OR NEW.staffage=NULL OR NEW.controlerNum=NULL)  THEN " +
					"SET @err =\"cardnum or staffnum or departmentname or staffname or gatenum or " +
					"periodofvalitystart or periodofvalityend or age or controlerNum is null.\";" +
					"ELSE " +
					"SET @cardnum=NEW.cardnum;" +
					"SET @staffnum=NEW.staffnum;" +
					"SET @departmentname=NEW.departmentname;" +
					"SET @staffname=NEW.staffname;" +
					"SET @gatenum=NEW.gatenum;" +
					"SET @periodofvalitystart=NEW.periodofvalitystart;" +
					"SET @periodofvalityend=NEW.periodofvalityend;" +
					"SET @staffage=NEW.staffage;" +
					"IF(NEW.picfilename=NULL)  THEN " +
					"SET @picfilename='dw';" +
					"ELSE " +
					"SET @picfilename=NEW.picfilename;" +
					"END IF;" +
					"IF NEW.controlerNum='ALL' THEN " +
					"SET @err =\"controlerNum is  'ALL'\"; " +
					"ELSE " +
					"SET @controlerNum=NEW.controlerNum;" +
					"SET @uuidnum=MD5(UUID());" +
					"SET @CMDSTRING=CONCAT_WS(',',CONCAT('{\"trans_id\":\"',@uuidnum,'\"')," +
					"'\"cmd_code\":\"KQ_UPDATE_USERINFO\"',CONCAT('\"idcard\":\"',@cardnum,'\"')," +
					"CONCAT('\"userid\":\"',@staffnum,'\"'),CONCAT('\"departmentname\":\"'," +
					"@departmentname,'\"'),CONCAT('\"name\":\"',@staffname,'\"')," +
					"CONCAT('\",\"gatenumber\":\"',@gatenum,'\"'),CONCAT('\"periodofvalitystart\":" +
					"\"',@periodofvalitystart,'\"'),CONCAT('\"periodofvalityend\":\"',@periodofvalityend," +
					"'\"'),CONCAT('\"staffage\":\"',@staffage,'\"'),CONCAT('\"picfilename\":\"'," +
					"@picfilename,'\"}'));INSERT INTO cmdform(controlerNum,transid,cmdstr) " +
					"VALUES(@controlerNum,@uuidnum,@CMDSTRING);" +
					"END IF;" +
					"END IF;" +
					"END;";
//					ResultSet rs1  = stmt.executeQuery(content);			
//					rs1.close();
//				System.out.println("Create TRIGGER:"+content);
				udresult  = stmt1.executeUpdate(content);
				if(stmt1!=null) stmt1.close();
//				System.out.println("Create TRIGGER:"+content);
//			}
		}
		rs.close();
/*		} catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try 
		{*/
		content="SELECT TRIGGER_NAME FROM information_schema.triggers WHERE TRIGGER_NAME='dwTRGupdatestaffjava';";
		rs = stmt.executeQuery(content);			
//			rs.close();
/*		} catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try 
		{*/
//		while(rs.next())
		if(!rs.last())
		{
//		    String tname = rs.getString(1);
//			if(tname.isEmpty())
//		    if(tname==null||tname=="")
//			{
				stmt1 = null;
/*					try 
					{*/
						stmt1 = conn.createStatement();
/*					} catch (SQLException e) 
					{
						// TODO Auto-generated catch block
//									e.printStackTrace();
						content="{\"res\":\"deny5\"}";
			        	sendReturnstr(content,response);
					}*/
				content="CREATE TRIGGER dwTRGupdatestaffjava AFTER UPDATE ON staffbasesetting FOR EACH ROW " +
					"BEGIN " +
					"IF(NEW.cardnum=NULL OR NEW.staffnum=NULL OR NEW.departmentname=NULL OR " +
						"NEW.staffname=NULL OR NEW.gatenum=NULL OR NEW.periodofvalitystart=NULL OR " +
						"NEW.periodofvalityend=NULL OR NEW.staffage=NULL OR NEW.controlerNum=NULL)  " +
					"THEN " +
					"SET @err =\"cardnum or staffnum or departmentname or staffname or gatenum or " +
						"periodofvalitystart or periodofvalityend or age or controlerNum is null.\";" +
					"ELSE " +
					"SET @cardnum=NEW.cardnum;" +
					"SET @staffnum=NEW.staffnum;" +
					"SET @departmentname=NEW.departmentname;" +
					"SET @staffname=NEW.staffname;" +
					"SET @gatenum=NEW.gatenum;" +
					"SET @periodofvalitystart=NEW.periodofvalitystart;" +
					"SET @periodofvalityend=NEW.periodofvalityend;" +
					"SET @staffage=NEW.staffage;" +
					"IF(NEW.picfilename=NULL)  THEN " +
					"SET @picfilename='dw';" +
					"ELSE " +
					"SET @picfilename=NEW.picfilename;" +
					"END IF;" +
					"IF NEW.controlerNum='ALL' THEN " +
					"SET @err =\"controlerNum is  'ALL'\"; " +
					"ELSE " +
					"SET @controlerNum=NEW.controlerNum;" +
					"SET @uuidnum=MD5(UUID());" +
					"SET @CMDSTRING=CONCAT_WS(',',CONCAT('{\"trans_id\":\"',@uuidnum,'\"')," +
						"'\"cmd_code\":\"KQ_UPDATE_USERINFO\"',CONCAT('\"idcard\":\"',@cardnum,'\"')," +
						"CONCAT('\"userid\":\"',@staffnum,'\"'),CONCAT('\"departmentname\":\"'," +
						"@departmentname,'\"'),CONCAT('\"name\":\"',@staffname,'\"')," +
						"CONCAT('\",\"gatenumber\":\"',@gatenum,'\"'),CONCAT('\"periodofvalitystart\":" +
						"\"',@periodofvalitystart,'\"'),CONCAT('\"periodofvalityend\":\"'," +
						"@periodofvalityend,'\"'),CONCAT('\"staffage\":\"',@staffage,'\"')," +
						"CONCAT('\"picfilename\":\"',@picfilename,'\"}'));" +
					"INSERT INTO cmdform(controlerNum,transid,cmdstr) VALUES(@controlerNum,@uuidnum," +
						"@CMDSTRING);" +
					"END IF;" +
					"END IF;" +
					"END;";
//					ResultSet rs1  = stmt.executeQuery(content);			
//					rs1.close();
				udresult  = stmt1.executeUpdate(content);
				if(stmt1!=null) stmt1.close();
//				System.out.println("Create TRIGGER:"+content);
//			}
		}
		rs.close();
/*		} catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try 
		{*/
		content="SELECT TRIGGER_NAME FROM information_schema.triggers WHERE TRIGGER_NAME='dwTRGdelstaffjava';";
		rs = stmt.executeQuery(content);			
//			rs.close();
/*		} catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try 
		{*/
//		while(rs.next())
		if(!rs.last())
		{
//			String tname = rs.getString(1);
//			if(tname.isEmpty())
//			{
				stmt1 = null;
//					try 
//					{
				stmt1 = conn.createStatement();
/*					} catch (SQLException e) 
					{
						// TODO Auto-generated catch block
//									e.printStackTrace();
						content="{\"res\":\"deny5\"}";
			        	sendReturnstr(content,response);
					}*/
				content="CREATE TRIGGER dwTRGdelstaffjava AFTER DELETE ON staffbasesetting FOR EACH ROW " +
						"BEGIN " +
						"IF(OLD.controlerNum=NULL OR OLD.cardnum=NULL OR OLD.controlerNum='ALL')  THEN " +
						"SET @err =\"cardnum or controlerNum is null or controlerNum is 'ALL'\";" +
						"ELSE " +
						"SET @controlerNumber=OLD.controlerNum;" +
						"SET @cardnumber=OLD.cardnum;" +
						"SET @uuidnumber=MD5(UUID());" +
						"SET @CMDSTR=CONCAT_WS(',',CONCAT('{\"trans_id\":\"',@uuidnumber,'\"')," +
						"'\"cmd_code\":\"KQ_DELETE_USERINFO\"',CONCAT('\"idcard\":\"'," +
						"@cardnumber,'\"}'));" +
						"INSERT INTO cmdform(controlerNum,transid,cmdstr) VALUES(@controlerNumber," +
						"@uuidnumber,@CMDSTR);" +
						"END IF;" +
						"END;";
//					ResultSet rs1  = stmt.executeQuery(content);			
//					rs1.close();
//				System.out.println("Create TRIGGER:"+content);
				udresult  = stmt1.executeUpdate(content);
				if(stmt1!=null) stmt1.close();
//			}
		}
		if(rs!=null) rs.close();
/*		} catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}*/
		if(stmt !=null) stmt.close();
		if(stmt1 !=null) stmt1.close();
		if(conn!=null) conn.close();
	}
	
	public void sendReturnstr(String content,HttpServletResponse response)
	{
		response.setContentType("text/html");
		PrintWriter out = null;
		try 
		{
			out = response.getWriter();
		} catch (IOException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		sun.misc.BASE64Encoder encoder = new sun.misc.BASE64Encoder();	
//        byte[] en = null;
//        content="{\"res\":\"deny5\"}";
        String en = encoder.encode(content.getBytes());
//        content=new String(en);
		out.println(en);
		out.flush();
		out.close();
//		return Userid;
	}

	private static void dwtimertask() 
	{  
        Runnable runnable = new Runnable() 
        {  
            public void run() 
            {  
                // task to run goes here
//            	System.out.println("run...  858");
        		//驱动程序名
        		try 
        		{
        			Class.forName("com.mysql.jdbc.Driver");
        		} catch (ClassNotFoundException e) 
        		{
        			System.out.println("com.mysql.jdbc.Driver Exception,863");
        		}
        		//声明Connection对象
        		Connection conn = null;
        		try 
        		{
        			//1.getConnection()方法，连接MySQL数据库
        			conn = DriverManager.getConnection(URL, NAME, PASSWORD);
        		} catch (SQLException e) 
        		{
        			// TODO Auto-generated catch block
//        						e.printStackTrace();
        			System.out.println("DriverManager.getConnection Exception,875");
        		}
        		//2.创建statement类对象，用来执行SQL语句
        		Statement stmt = null;
        		try 
        		{
        			stmt = conn.createStatement();
        		} catch (SQLException e) 
        		{
        			// TODO Auto-generated catch block
//        						e.printStackTrace();
        			System.out.println("conn.createStatement Exception,886");
        		}
        		//3.ResultSet类，用来存放获取的结果集
        		ResultSet rs = null;
        		int udresult  = 0;
        		String content = null;
        		try 
        		{
        			content="SELECT controlerNum FROM gatesetting WHERE " +
        					"((select unix_timestamp(now()))-(select unix_timestamp(lasttime)))>120;";
        			rs = stmt.executeQuery(content);			
        		} catch (SQLException e) 
        		{
        			// TODO Auto-generated catch block
//        			e.printStackTrace();
        			System.out.println("stmt.executeQuery Exception,901");
        		}
        		try 
        		{
        			while(rs.next())
        			{
        				String controlerNum = rs.getString(1);
        				if(!controlerNum.isEmpty())
        				{
    						Statement stmt1 = null;
    						try 
    						{
    							stmt1 = conn.createStatement();
    						} catch (SQLException e) 
    						{
    							// TODO Auto-generated catch block
//        										e.printStackTrace();
    							System.out.println("conn.createStatement Exception,918");
    						}
    						content="UPDATE gatesetting SET stat= 'offline'  WHERE controlerNum ='"+controlerNum+"'";
//        						ResultSet rs1  = stmt.executeQuery(content);			
//        						rs1.close();
    						udresult  = stmt1.executeUpdate(content);
    						if(stmt1!=null) stmt1.close();
        				}
        			}
        			rs.close();
        		} catch (SQLException e) 
        		{
        			// TODO Auto-generated catch block
        //			e.printStackTrace();
//        			System.out.println("rs.next Exception,932");
        		}
        		// 关闭资源
                try
                {
                    if(stmt!=null) stmt.close();
                }catch(SQLException se2)
                {
                }// 什么都不做
                try
                {
                    if(conn!=null) conn.close();
                }catch(SQLException se)
                {
                    se.printStackTrace();
                }
            }  
        };  
        ScheduledExecutorService service = Executors  
                .newSingleThreadScheduledExecutor();  
        // 第二个参数为首次执行的延时时间，第三个参数为定时执行的间隔时间  
        service.scheduleAtFixedRate(runnable, 10, 10, TimeUnit.SECONDS);  
    } 
	
	private void dwgetgatetab(HttpServletResponse response,String content) throws SQLException, IOException, JSONException
	{
		System.out.println("dwgetgatetab: "+ content);
		
		JSONObject json = null;
		json = new JSONObject(content);

    	String controlerNum=json.getString("ControlerNum");
		String gatenum=json.getString("gatenum");
		
		try 
		{
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) 
		{
			System.out.println("com.mysql.jdbc.Driver Exception,109");
		}
		Connection conn = null;
		conn = DriverManager.getConnection(URL, NAME, PASSWORD);
		Statement stmt = null;
		stmt = conn.createStatement();
		ResultSet rs = null;
		String sqlstr=null;
		if(gatenum.equals("all"))
		{
			sqlstr="select gatename,region,controlmethod,delayopentime,delayclosetime,sensorstat,attendancecheck,antipassback from gatesetting WHERE controlerNum= '"+controlerNum+"' AND gatenum='0';";
		}else
		{
			sqlstr="select gatename,region,controlmethod,delayopentime,delayclosetime,sensorstat,attendancecheck,antipassback from gatesetting WHERE controlerNum= '"+controlerNum+"' AND gatenum='"+gatenum+"';";
		}
		rs = stmt.executeQuery(sqlstr);
		String gatetab = null;
		int i=1;
		int j=0;
		while(rs.next())
		{			
			for(j=0;j<8;j++)
			{			   
				if(i==1 && j==0)
			    {			        
					gatetab = "{\"s"+i+"_"+j+"\":\""+rs.getString(j+1)+"\"";						
			    }else
			    {
			    	gatetab += ",\"s"+i+"_"+j+"\":\""+rs.getString(j+1)+"\"";
			    }
			}
	        i++;	
		}
		if(rs!=null) rs.close();
		if(stmt != null) stmt.close();
		if(conn!=null) conn.close();
		if(i==1)
		{
		    for(j=0;j<8;j++)
			{
			    if(j==0)
				{  
			    	gatetab = "{\"s1_"+j+"\":\" \"";	
			    }else
				{
			    	gatetab += ",\"s1_"+j+"\":\" \"";
				}
			}
		}	
		gatetab += ",\"nrows\":\""+(i-1)+"\",\"ncols\":\"8\"}";
	    response.setContentType("text/html;charset=utf-8");
		response.setCharacterEncoding("UTF-8");
		PrintWriter out = response.getWriter();
		out.println(gatetab);
		out.flush();
		out.close();
	}
	
	private void dwdowngate(HttpServletResponse response,String content) throws SQLException, IOException
	{
		System.out.println("dwdowngate: "+ content);
		JSONObject json = null;
    	try 
    	{
			json = new JSONObject(content);
		} catch (JSONException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	String gatenum = null;
    	String gatename = null;
    	String region = null;
    	String controlmethod = null;
    	String delayopentime = null;
    	String delayclosetime = null;
    	String sensorstat = null;
    	String attendancecheck = null;
    	String antipassback = null;
    	String controlerNum = null;
		try 
		{
			gatenum = json.getString("gatenum");
			gatename = json.getString("gatename");
			region = json.getString("region");
			controlmethod = json.getString("controlmethod");
			delayopentime = json.getString("delayopentime");
			delayclosetime = json.getString("delayclosetime");
			sensorstat = json.getString("sensorstat");
			attendancecheck = json.getString("attendancecheck");
			antipassback = json.getString("antipassback");
			controlerNum = json.getString("ControlerNum");
		} catch (JSONException e1) 
		{
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		if(gatenum==null||gatenum==""||gatename==null||gatename==""||region==null||region==""||controlmethod==null||controlmethod==""||delayopentime==null||delayopentime==""||delayclosetime==null||delayclosetime==""||sensorstat==null||sensorstat==""||attendancecheck==null||attendancecheck==""||antipassback==null||antipassback==""||controlerNum==null||controlerNum=="")
		{
			response.setContentType("text/html;charset=utf-8");
			response.setCharacterEncoding("UTF-8");
			PrintWriter out = response.getWriter();
			out.println("fail01");
			out.flush();
			out.close();
			return;
		}
		try 
		{
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) 
		{
			System.out.println("com.mysql.jdbc.Driver Exception,109");
		}
		Connection conn = null;
		conn = DriverManager.getConnection(URL, NAME, PASSWORD);
		Statement stmt = null;
		stmt = conn.createStatement();
		ResultSet rs = null;
		Statement stmt1 = null;
//		stmt1 = conn.createStatement();		
		String sqlstr = null;
		ResultSet rs1 = null;
//		Statement stmt2 = null;
//		stmt2= conn.createStatement();
//		ResultSet rs2 = null;
		int udresult = 0;
		String cmdstr=null;
		if(controlerNum=="ALL")
		{
			sqlstr="select distinct controlerNum from gatesetting ORDER BY lasttime DESC;";
			rs = stmt.executeQuery(sqlstr);
			while(rs.next())
			{	
//				if(gatenum=="all")
				if(gatenum.equals("all"))
				{
					int i=0;
					for(i=0;i<4;i++)
					{
						Statement stmt3 = null;
						stmt3= conn.createStatement();
						sqlstr="UPDATE gatesetting SET gatename='"+gatename+"',controlmethod='"+controlmethod+"',delayopentime='"+delayopentime+"',delayclosetime='"+delayclosetime+"',sensorstat='"+sensorstat+"',attendancecheck='"+attendancecheck+"',region='"+region+"',antipassback='"+antipassback+"' WHERE controlerNum = '"+rs.getString(1)+"' AND gatenum= '"+i+"';";						
						udresult  = stmt3.executeUpdate(sqlstr);
						if(stmt3!=null) stmt3.close();
						sqlstr="select IP,PORT,wIP,wPORT,gatename,region,controlmethod,delayopentime,delayclosetime,sensorstat,attendancecheck,antipassback from gatesetting WHERE controlerNum= '"+rs.getString(1)+"' AND gatenum='"+i+"';";
						stmt1= conn.createStatement();
						rs1 = stmt1.executeQuery(sqlstr);
						rs1.last();
						String transid=UUID.randomUUID().toString().replace("-", "");
						cmdstr="{\"trans_id\":\""+transid+"\",\"cmd_code\":\"KQ_SET_ DEVICEINFO\",\"IP\":\""+rs1.getString(1)+"\",\"PORT\":\""+rs1.getString(2)+"\",\"wIP\":\""+rs1.getString(3)+"\",\"wPORT\":\""+rs1.getString(4)+"\",\"gatename\":\""+rs1.getString(5)+"\",\"controlmethod\":\""+rs1.getString(7)+"\",\"delayopentime\":\""+rs1.getString(8)+"\",\"delayclosetime\":\""+rs1.getString(9)+"\",\"sensorstat\":\""+rs1.getString(10)+"\",\"gatenumber\":\""+i+"\",\"readerfunction\":\"26\",\"attendancecheck\":\""+rs1.getString(11)+"\",\"region\":\""+rs1.getString(6)+"\",\"password\":\"888888\",\"forcepass\":\"999999\",\"antipassback\":\""+rs1.getString(12)+"\"}";					
						sqlstr="INSERT INTO cmdform(controlerNum,transid,cmdstr) VALUES('"+rs.getString(1)+"','"+transid+"','"+cmdstr+"');";
						stmt3= conn.createStatement();
						udresult  = stmt3.executeUpdate(sqlstr);
						if(rs1!=null) rs1.close();
						if(stmt1!=null) stmt1.close();
						if(stmt3!=null) stmt3.close();
					}
				}else
				{
					Statement stmt3 = null;
					stmt3= conn.createStatement();
					sqlstr="UPDATE gatesetting SET gatename='"+gatename+"',controlmethod='"+controlmethod+"',delayopentime='"+delayopentime+"',delayclosetime='"+delayclosetime+"',sensorstat='"+sensorstat+"',attendancecheck='"+attendancecheck+"',region='"+region+"',antipassback='"+antipassback+"' WHERE controlerNum = '"+rs.getString(1)+"' AND gatenum= '"+gatenum+"';";
					udresult  = stmt3.executeUpdate(sqlstr);
					if(stmt3!=null) stmt3.close();
					sqlstr="select IP,PORT,wIP,wPORT,gatename,region,controlmethod,delayopentime,delayclosetime,sensorstat,attendancecheck,antipassback from gatesetting WHERE controlerNum= '"+rs.getString(1)+"' AND gatenum='"+gatenum+"';";
					stmt1= conn.createStatement();
					rs1 = stmt1.executeQuery(sqlstr);
					rs1.last();
					String transid=UUID.randomUUID().toString().replace("-", "");
					cmdstr="{\"trans_id\":\""+transid+"\",\"cmd_code\":\"KQ_SET_ DEVICEINFO\",\"IP\":\""+rs1.getString(1)+"\",\"PORT\":\""+rs1.getString(2)+"\",\"wIP\":\""+rs1.getString(3)+"\",\"wPORT\":\""+rs1.getString(4)+"\",\"gatename\":\""+rs1.getString(5)+"\",\"controlmethod\":\""+rs1.getString(7)+"\",\"delayopentime\":\""+rs1.getString(8)+"\",\"delayclosetime\":\""+rs1.getString(9)+"\",\"sensorstat\":\""+rs1.getString(10)+"\",\"gatenumber\":\""+gatenum+"\",\"readerfunction\":\"26\",\"attendancecheck\":\""+rs1.getString(11)+"\",\"region\":\""+rs1.getString(6)+"\",\"password\":\"888888\",\"forcepass\":\"999999\",\"antipassback\":\""+rs1.getString(12)+"\"}";					
					sqlstr="INSERT INTO cmdform(controlerNum,transid,cmdstr) VALUES('"+rs.getString(1)+"','"+transid+"','"+cmdstr+"');";
					stmt3= conn.createStatement();
					udresult  = stmt3.executeUpdate(sqlstr);
					if(rs1!=null) rs1.close();
					if(stmt1!=null) stmt1.close();
					if(stmt3!=null) stmt3.close();
				}
			}
			if(rs!=null) rs.close();
			if(stmt!=null) stmt.close();
		}else
		{
			sqlstr="SELECT COUNT(*) FROM gatesetting WHERE controlerNum= '"+controlerNum+"';";
			rs = stmt.executeQuery(sqlstr);
			rs.last();
			if(rs.getInt(1)!=0)
			{
//				if(gatenum=="all")
				if(gatenum.equals("all"))
				{
					int i=0;
					for(i=0;i<4;i++)
					{
						Statement stmt3 = null;
						stmt3= conn.createStatement();
						sqlstr="UPDATE gatesetting SET gatename='"+gatename+"',controlmethod='"+controlmethod+"',delayopentime='"+delayopentime+"',delayclosetime='"+delayclosetime+"',sensorstat='"+sensorstat+"',attendancecheck='"+attendancecheck+"',region='"+region+"',antipassback='"+antipassback+"' WHERE controlerNum = '"+controlerNum+"' AND gatenum= '"+i+"';";
						udresult  = stmt3.executeUpdate(sqlstr);
						if(stmt3!=null) stmt3.close();
						sqlstr="select IP,PORT,wIP,wPORT,gatename,region,controlmethod,delayopentime,delayclosetime,sensorstat,attendancecheck,antipassback from gatesetting WHERE controlerNum= '"+controlerNum+"' AND gatenum='"+i+"';";
						stmt1= conn.createStatement();
						rs1 = stmt1.executeQuery(sqlstr);
						rs1.last();
						String transid=UUID.randomUUID().toString().replace("-", "");
						cmdstr="{\"trans_id\":\""+transid+"\",\"cmd_code\":\"KQ_SET_ DEVICEINFO\",\"IP\":\""+rs1.getString(1)+"\",\"PORT\":\""+rs1.getString(2)+"\",\"wIP\":\""+rs1.getString(3)+"\",\"wPORT\":\""+rs1.getString(4)+"\",\"gatename\":\""+rs1.getString(5)+"\",\"controlmethod\":\""+rs1.getString(7)+"\",\"delayopentime\":\""+rs1.getString(8)+"\",\"delayclosetime\":\""+rs1.getString(9)+"\",\"sensorstat\":\""+rs1.getString(10)+"\",\"gatenumber\":\""+i+"\",\"readerfunction\":\"26\",\"attendancecheck\":\""+rs1.getString(11)+"\",\"region\":\""+rs1.getString(6)+"\",\"password\":\"888888\",\"forcepass\":\"999999\",\"antipassback\":\""+rs1.getString(12)+"\"}";					
						sqlstr="INSERT INTO cmdform(controlerNum,transid,cmdstr) VALUES('"+controlerNum+"','"+transid+"','"+cmdstr+"');";
						stmt3= conn.createStatement();
						udresult  = stmt3.executeUpdate(sqlstr);
						if(rs1!=null) rs1.close();
						if(stmt1!=null) stmt1.close();
						if(stmt3!=null) stmt3.close();
					}
				}else
				{
					Statement stmt3 = null;
					stmt3= conn.createStatement();
					sqlstr="UPDATE gatesetting SET gatename='"+gatename+"',controlmethod='"+controlmethod+"',delayopentime='"+delayopentime+"',delayclosetime='"+delayclosetime+"',sensorstat='"+sensorstat+"',attendancecheck='"+attendancecheck+"',region='"+region+"',antipassback='"+antipassback+"' WHERE controlerNum = '"+controlerNum+"' AND gatenum= '"+gatenum+"';";
					udresult  = stmt3.executeUpdate(sqlstr);
					if(stmt3!=null) stmt3.close();
					sqlstr="select IP,PORT,wIP,wPORT,gatename,region,controlmethod,delayopentime,delayclosetime,sensorstat,attendancecheck,antipassback from gatesetting WHERE controlerNum= '"+controlerNum+"' AND gatenum='"+gatenum+"';";
					System.out.println("sqlstr: " + sqlstr);
					stmt1= conn.createStatement();
					rs1 = stmt1.executeQuery(sqlstr);
					rs1.last();
					String transid=UUID.randomUUID().toString().replace("-", "");
					cmdstr="{\"trans_id\":\""+transid+"\",\"cmd_code\":\"KQ_SET_ DEVICEINFO\",\"IP\":\""+rs1.getString(1)+"\",\"PORT\":\""+rs1.getString(2)+"\",\"wIP\":\""+rs1.getString(3)+"\",\"wPORT\":\""+rs1.getString(4)+"\",\"gatename\":\""+rs1.getString(5)+"\",\"controlmethod\":\""+rs1.getString(7)+"\",\"delayopentime\":\""+rs1.getString(8)+"\",\"delayclosetime\":\""+rs1.getString(9)+"\",\"sensorstat\":\""+rs1.getString(10)+"\",\"gatenumber\":\""+gatenum+"\",\"readerfunction\":\"26\",\"attendancecheck\":\""+rs1.getString(11)+"\",\"region\":\""+rs1.getString(6)+"\",\"password\":\"888888\",\"forcepass\":\"999999\",\"antipassback\":\""+rs1.getString(12)+"\"}";
					System.out.println("cmdstr: " + cmdstr);
					sqlstr="INSERT INTO cmdform(controlerNum,transid,cmdstr) VALUES('"+controlerNum+"','"+transid+"','"+cmdstr+"');";
					stmt3= conn.createStatement();
					udresult  = stmt3.executeUpdate(sqlstr);
					if(rs1!=null) rs1.close();
					if(stmt3!=null) stmt3.close();
				}
			}else
			{
				if(gatenum=="all")
				{
					int i=0;
					for(i=0;i<4;i++)
					{
						Statement stmt3 = null;
						stmt3= conn.createStatement();
						sqlstr="INSERT INTO gatesetting(controlerNum,gatenum,gatename,controlmethod,delayopentime,delayclosetime,sensorstat,attendancecheck,region,antipassback) VALUES('"+controlerNum+"','"+i+"','"+gatename+"','"+controlmethod+"','"+delayopentime+"','"+delayclosetime+"','"+sensorstat+"','"+attendancecheck+"','"+region+"','"+antipassback+"');";						
						udresult  = stmt3.executeUpdate(sqlstr);
						if(stmt3!=null) stmt3.close();
						sqlstr="select IP,PORT,wIP,wPORT,gatename,region,controlmethod,delayopentime,delayclosetime,sensorstat,attendancecheck,antipassback from gatesetting WHERE controlerNum= '"+controlerNum+"' AND gatenum='"+i+"';";
						stmt1= conn.createStatement();
						rs1 = stmt1.executeQuery(sqlstr);
						rs1.last();
						String transid=UUID.randomUUID().toString().replace("-", "");
						cmdstr="{\"trans_id\":\""+transid+"\",\"cmd_code\":\"KQ_SET_ DEVICEINFO\",\"IP\":\""+rs1.getString(1)+"\",\"PORT\":\""+rs1.getString(2)+"\",\"wIP\":\""+rs1.getString(3)+"\",\"wPORT\":\""+rs1.getString(4)+"\",\"gatename\":\""+rs1.getString(5)+"\",\"controlmethod\":\""+rs1.getString(7)+"\",\"delayopentime\":\""+rs1.getString(8)+"\",\"delayclosetime\":\""+rs1.getString(9)+"\",\"sensorstat\":\""+rs1.getString(10)+"\",\"gatenumber\":\""+i+"\",\"readerfunction\":\"26\",\"attendancecheck\":\""+rs1.getString(11)+"\",\"region\":\""+rs1.getString(6)+"\",\"password\":\"888888\",\"forcepass\":\"999999\",\"antipassback\":\""+rs1.getString(12)+"\"}";					
						sqlstr="INSERT INTO cmdform(controlerNum,transid,cmdstr) VALUES('"+controlerNum+"','"+transid+"','"+cmdstr+"');";
						stmt3= conn.createStatement();
						udresult  = stmt3.executeUpdate(sqlstr);
						if(rs1!=null) rs1.close();
						if(stmt1!=null) stmt1.close();
						if(stmt3!=null) stmt3.close();
					}
				}else
				{
					Statement stmt3 = null;
					stmt3= conn.createStatement();
					sqlstr="INSERT INTO gatesetting(controlerNum,gatenum,gatename,controlmethod,delayopentime,delayclosetime,sensorstat,attendancecheck,region,antipassback) VALUES('"+controlerNum+"','"+gatenum+"','"+gatename+"','"+controlmethod+"','"+delayopentime+"','"+delayclosetime+"','"+sensorstat+"','"+attendancecheck+"','"+region+"','"+antipassback+"');";
					udresult  = stmt3.executeUpdate(sqlstr);
					if(stmt3!=null) stmt3.close();
					sqlstr="select IP,PORT,wIP,wPORT,gatename,region,controlmethod,delayopentime,delayclosetime,sensorstat,attendancecheck,antipassback from gatesetting WHERE controlerNum= '"+controlerNum+"' AND gatenum='"+gatenum+"';";
					stmt1= conn.createStatement();
					rs1 = stmt1.executeQuery(sqlstr);
					rs1.last();
					String transid=UUID.randomUUID().toString().replace("-", "");
					cmdstr="{\"trans_id\":\""+transid+"\",\"cmd_code\":\"KQ_SET_ DEVICEINFO\",\"IP\":\""+rs1.getString(1)+"\",\"PORT\":\""+rs1.getString(2)+"\",\"wIP\":\""+rs1.getString(3)+"\",\"wPORT\":\""+rs1.getString(4)+"\",\"gatename\":\""+rs1.getString(5)+"\",\"controlmethod\":\""+rs1.getString(7)+"\",\"delayopentime\":\""+rs1.getString(8)+"\",\"delayclosetime\":\""+rs1.getString(9)+"\",\"sensorstat\":\""+rs1.getString(10)+"\",\"gatenumber\":\""+gatenum+"\",\"readerfunction\":\"26\",\"attendancecheck\":\""+rs1.getString(11)+"\",\"region\":\""+rs1.getString(6)+"\",\"password\":\"888888\",\"forcepass\":\"999999\",\"antipassback\":\""+rs1.getString(12)+"\"}";					
					sqlstr="INSERT INTO cmdform(controlerNum,transid,cmdstr) VALUES('"+controlerNum+"','"+transid+"','"+cmdstr+"');";
					stmt3= conn.createStatement();
					udresult  = stmt3.executeUpdate(sqlstr);
					if(rs1!=null) rs1.close();
					if(stmt3!=null) stmt3.close();
				}
			}
		}
		if(conn!=null) conn.close();
	    response.setContentType("text/html;charset=utf-8");
		response.setCharacterEncoding("UTF-8");
		PrintWriter out = response.getWriter();
		if(udresult==1)
		{
			out.println("success");
		}else
		{
			out.println("fail");
		}
		out.flush();
		out.close();
	}
	
	private void dwdownserver(HttpServletResponse response,String content) throws SQLException, IOException
	{
//		System.out.println("dwdownserver: "+ content);
		JSONObject json = null;
    	try 
    	{
			json = new JSONObject(content);
		} catch (JSONException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	String WebADDR = null;
    	String WebPORT = null;
    	String Camera0ADDR = null;
    	String Camera0PORT = null;
    	String Camera1ADDR = null;
    	String Camera1PORT = null;
    	String Camera2ADDR = null;
    	String Camera2PORT = null;
    	String Camera3ADDR = null;
    	String Camera3PORT = null;
    	String cmdresultpath = null;
    	String realtimepath = null;
    	String DetectDevicepath = null;
    	String reQusttime = null;
		try 
		{
			WebADDR = json.getString("WebADDR");
			WebPORT = json.getString("WebPORT");
			Camera0ADDR = json.getString("Camera0ADDR");
			Camera0PORT = json.getString("Camera0PORT");
			Camera1ADDR = json.getString("Camera1ADDR");
			Camera1PORT = json.getString("Camera1PORT");
			Camera2ADDR = json.getString("Camera2ADDR");
			Camera2PORT = json.getString("Camera2PORT");
			Camera3ADDR = json.getString("Camera3ADDR");
			Camera3PORT = json.getString("Camera3PORT");
			realtimepath = json.getString("realtimepath");
			DetectDevicepath = json.getString("DetectDevicepath");
			cmdresultpath = json.getString("cmdresultpath");
			reQusttime = json.getString("reQusttime");
		} catch (JSONException e1) 
		{
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		if(WebADDR==null||WebADDR==""||WebPORT==null||WebPORT==""||cmdresultpath==null||cmdresultpath==""||realtimepath==null||realtimepath==""||DetectDevicepath==null||DetectDevicepath==""||Camera0ADDR==null||Camera0ADDR==""||Camera0PORT==null||Camera0PORT==""||Camera1ADDR==null||Camera1ADDR==""||Camera1PORT==null||Camera1PORT==""||Camera2ADDR==null||Camera2ADDR==""||Camera2PORT==null||Camera2PORT==""||Camera3ADDR==null||Camera3ADDR==""||Camera3PORT==null||Camera3PORT==""||reQusttime==null||reQusttime=="")
		{
			response.setContentType("text/html;charset=utf-8");
			response.setCharacterEncoding("UTF-8");
			PrintWriter out = response.getWriter();
			out.println("fail01");
			out.flush();
			out.close();
			return;
		}
		try 
		{
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) 
		{
			System.out.println("com.mysql.jdbc.Driver Exception,109");
		}
		Connection conn = null;
		conn = DriverManager.getConnection(URL, NAME, PASSWORD);
		Statement stmt = null;
		stmt = conn.createStatement();
		ResultSet rs = null;
		Statement stmt1 = null;
		stmt1 = conn.createStatement();		
		String sqlstr = null;
		ResultSet rs1 = null;
		Statement stmt3 = null;
		stmt3 = conn.createStatement();
		ResultSet rs3 = null;
		sqlstr="SELECT COUNT(*) FROM serversetting;";
		rs3 = stmt3.executeQuery(sqlstr);
		rs3.last();
		if(rs3.getInt(1)!=0)
		{
			sqlstr="UPDATE serversetting SET serverIP= '"+WebADDR+"',serverPORT='"+WebPORT+"',cmdresultpath='"+cmdresultpath+"',realtimepath='"+realtimepath+"',DetectDevicepath='"+DetectDevicepath+"',Camera0ADDR='"+Camera0ADDR+"',Camera0PORT='"+Camera0PORT+"',Camera1ADDR='"+Camera1ADDR+"',Camera1PORT='"+Camera1PORT+"',Camera2ADDR='"+Camera2ADDR+"',Camera2PORT='"+Camera2PORT+"',Camera3ADDR='"+Camera3ADDR+"',Camera3PORT='"+Camera3PORT+"',reQusttime='"+reQusttime+"';";
		}else
		{
			sqlstr="INSERT INTO serversetting(serverIP,serverPORT,cmdresultpath,realtimepath,DetectDevicepath,Camera0ADDR,Camera0PORT,Camera1ADDR,Camera1PORT,Camera2ADDR,Camera2PORT,Camera3ADDR,Camera3PORT,reQusttime) VALUES('"+WebADDR+"','"+WebPORT+"','"+cmdresultpath+"','"+realtimepath+"','"+DetectDevicepath+"','"+Camera0ADDR+"','"+Camera0PORT+"','"+Camera1ADDR+"','"+Camera1PORT+"','"+Camera2ADDR+"','"+Camera2PORT+"','"+Camera3ADDR+"','"+Camera3PORT+"','"+reQusttime+"');";

		}
		Statement stmt4 = null;
		stmt4 = conn.createStatement();
		int udresult = 0;
		udresult  = stmt4.executeUpdate(sqlstr);
		
		if(rs3!=null) rs3.close();
		if(stmt3 != null) stmt3.close();
		if(stmt4 != null) stmt4.close();
		
		sqlstr="select serverIP,serverPORT,cmdresultpath,realtimepath,DetectDevicepath,Camera0ADDR," +
			"Camera0PORT,Camera1ADDR,Camera1PORT,Camera2ADDR,Camera2PORT,Camera3ADDR,Camera3PORT,reQusttime " +
			"from serversetting;";
		rs = stmt.executeQuery(sqlstr);
		String transid = null;
		String cmdstr = null;		
		rs.last();			
		sqlstr="SELECT DISTINCT controlerNum FROM gatesetting WHERE controlerNum !='ALL';";
		rs1 = stmt1.executeQuery(sqlstr);
		while(rs1.next())
		{				
			transid=UUID.randomUUID().toString().replace("-", "");
			cmdstr="{\"trans_id\":\""+transid+"\",\"cmd_code\":\"DW_SET_WEB_SERVERINFO\",\"url\":\""+rs.getString(1)+
				"\",\"port\":\""+rs.getString(2)+"\",\"cmdresultpath\":\""+rs.getString(3)+
				"\",\"realtimepath\":\""+rs.getString(4)+"\",\"DetectDevicepath\":\""+rs.getString(5)+
				"\",\"Camera0ADDR\":\""+rs.getString(6)+"\",\"Camera0PORT\":\""+rs.getString(7)+
				"\",\"Camera1ADDR\":\""+rs.getString(8)+"\",\"Camera1PORT\":\""+rs.getString(9)+
				"\",\"Camera2ADDR\":\""+rs.getString(10)+"\",\"Camera2PORT\":\""+rs.getString(11)+
				"\",\"Camera3ADDR\":\""+rs.getString(12)+"\",\"Camera3PORT\":\""+rs.getString(13)+
				"\",\"request_time\":\""+rs.getString(14)+"\"}";					
			Statement stmt2 = null;
			sqlstr="INSERT INTO cmdform(controlerNum,transid,cmdstr) VALUES('"+rs1.getString(1)+"','"+transid+"','"+cmdstr+"');";
			stmt2 = conn.createStatement();
			udresult  = stmt2.executeUpdate(sqlstr);
			if(stmt2 != null) stmt2.close();
		}		
		if(rs!=null) rs.close();
		if(rs1!=null) rs1.close();
		if(stmt != null) stmt.close();
		if(stmt1 != null) stmt1.close();
		if(conn!=null) conn.close();
	    response.setContentType("text/html;charset=utf-8");
		response.setCharacterEncoding("UTF-8");
		PrintWriter out = response.getWriter();
		if(udresult==1)
		{
			out.println("success");
		}else
		{
			out.println("fail");
		}
		out.flush();
		out.close();
	}
	
	private void dwgetservertab(HttpServletResponse response) throws SQLException, IOException
	{
		try 
		{
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) 
		{
			System.out.println("com.mysql.jdbc.Driver Exception,109");
		}
		Connection conn = null;
		conn = DriverManager.getConnection(URL, NAME, PASSWORD);
		Statement stmt = null;
		stmt = conn.createStatement();
		ResultSet rs = null;
	
		String sqlstr="select serverIP,serverPORT,cmdresultpath,realtimepath,DetectDevicepath,Camera0ADDR," +
			"Camera0PORT,Camera1ADDR,Camera1PORT,Camera2ADDR,Camera2PORT,Camera3ADDR,Camera3PORT,reQusttime," +
			"sVersion from serversetting;";
		rs = stmt.executeQuery(sqlstr);
		String servertab = null;
		int i=1;
		int j=0;
		while(rs.next())
		{			
			for(j=0;j<15;j++)
			{			   
				if(i==1 && j==0)
			    {			        
					servertab = "{\"s"+i+"_"+j+"\":\""+rs.getString(j+1)+"\"";						
			    }else
			    {
			        servertab += ",\"s"+i+"_"+j+"\":\""+rs.getString(j+1)+"\"";
			    }
			}
	        i++;	
		}
		if(rs!=null) rs.close();
		if(stmt != null) stmt.close();
		if(conn!=null) conn.close();
		if(i==1)
		{
		    for(j=0;j<15;j++)
			{
			    if(j==0)
				{  
				    servertab = "{\"s1_"+j+"\":\" \"";	
			    }else
				{
				    servertab += ",\"s1_"+j+"\":\" \"";
				}
			}
		}	
	    servertab += ",\"nrows\":\""+(i-1)+"\",\"ncols\":\"15\"}";
	    response.setContentType("text/html;charset=utf-8");
		response.setCharacterEncoding("UTF-8");
		PrintWriter out = response.getWriter();
		out.println(servertab);
		out.flush();
		out.close();
	}
	
	private void dwviewstaff(HttpServletResponse response,int skid) throws SQLException, IOException
	{
		try 
		{
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) 
		{
			System.out.println("com.mysql.jdbc.Driver Exception,109");
		}
		Connection conn = null;
		conn = DriverManager.getConnection(URL, NAME, PASSWORD);
		Statement stmt = null;
		stmt = conn.createStatement();
		ResultSet rs = null;
		String sqlstr="select staffnum,staffname,staffgender,staffage,picfilename,entrancevalitystart," +
			"entrancevalityend,leavevalitystart,leavevalityend from staffbasesetting where skid="+skid;
		rs = stmt.executeQuery(sqlstr);
		String returnstr = null;
		while(rs.next())
		{
			returnstr="{\"staffnum\":\""+rs.getString(1)+"\",\"staffname\":\""+rs.getString(2)+
				"\",\"staffgender\":\""+rs.getString(3)+"\",\"staffage\":\""+rs.getString(4)+
				"\",\"picfilename\":\""+rs.getString(5)+"\",\"entrancevality\":\""+rs.getString(6)+
				"-"+rs.getString(7)+"\",\"leavevality\":\""+rs.getString(8)+"-"+rs.getString(9)+"\"}";
		}
		if(rs!=null) rs.close();
		if(stmt != null) stmt.close();
		if(conn!=null) conn.close();
		response.setContentType("text/html;charset=utf-8");
		response.setCharacterEncoding("UTF-8");
		PrintWriter out = response.getWriter();
		out.println(returnstr);
		out.flush();
		out.close();
	}
	
	private void dwdelstaff(HttpServletResponse response,int skip) throws SQLException, IOException
	{
		try 
		{
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) 
		{
			System.out.println("com.mysql.jdbc.Driver Exception,109");
		}
		Connection conn = null;
		conn = DriverManager.getConnection(URL, NAME, PASSWORD);
		Statement stmt = null;
		stmt = conn.createStatement();
		String sqlstr="delete from staffbasesetting where skid="+skip+";";
		int udresult  = stmt.executeUpdate(sqlstr);
		if(stmt != null) stmt.close();
		if(conn!=null) conn.close();
/*		response.setContentType("text/html;charset=utf-8");
		response.setCharacterEncoding("UTF-8");
		PrintWriter out = response.getWriter();
		if(udresult==1)
		{	
			String returnstr = "<script language=\"JavaScript\">alert('删除"+skip+"成功。');</script><br/>";
			out.println(returnstr);
//			out.println("<script language=\"JavaScript\">self.setTimeout(window.location.href='/door/staffbasesetting.php',5000);</script><br/>");			
		}else
		{
			String returnstr = "<script language=\"JavaScript\">alert('删除"+skip+"失败。');</script><br/>";
			out.println(returnstr);
		}
		out.flush();
		out.close();*/
	}
	
	private void dwaddstaff(HttpServletResponse response,Map <String,String> map) throws SQLException, IOException
	{
		String controlerNum = (String)map.get("controlerNum");
    	String staffnum =  (String)map.get("staffnum");
    	String staffname = (String)map.get("staffname");
//    	String staffname = new String(staffname1.getBytes("iso-8859-1"), "gb2312");
//    	String staffname = new String(staffname1.getBytes("iso-8859-1"), "utf-8");
    	String cardnum = (String)map.get("cardnum");
    	String departmentname =  (String)map.get("departmentname");
    	String departmentID = (String)map.get("departmentID");
    	String gatenum = (String)map.get("gateName");
    	String periodofvalitystart =  (String)map.get("periodofvalitystart");
    	String periodofvalityend = (String)map.get("periodofvalityend");
    	String entrancevalitystart = (String)map.get("entrancevalitystart");
    	String entrancevalityend =  (String)map.get("entrancevalityend");
    	String leavevalitystart = (String)map.get("leavevalitystart");
    	String leavevalityend = (String)map.get("leavevalityend");
    	String staffgender = (String)map.get("staffgender");
    	String staffage =  (String)map.get("staffage");
    	String picfilename = (String)map.get("picfilename");
    	String limitenrancenum ="";
    	String limitleavenum ="";
/*    	System.out.println("controlerNum: "+ controlerNum+
		           " staffnum: "+staffnum+
		           " staffname: "+staffname+
		           " cardnum: "+cardnum+
		           " departmentname: "+departmentname+
		           " departmentID: "+departmentID+
		           " gateName: "+gateName+
		           " periodofvalitystart: "+periodofvalitystart+
		           " periodofvalityend: "+periodofvalityend+
		           " entrancevalitystart: "+entrancevalitystart+
		           " entrancevalityend: "+entrancevalityend+
		           " leavevalitystart: "+leavevalitystart+
		           " leavevalityend: "+leavevalityend+
		           " staffgender: "+staffgender+
		           " staffage: "+staffage+
		           " picfilename: "+picfilename);*/
    	try 
		{
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) 
		{
			System.out.println("com.mysql.jdbc.Driver Exception,109");
		}
    	//����Connection����
		Connection conn = null;
		//1.getConnection()����������MySQL��ݿ�
		conn = DriverManager.getConnection(URL, NAME, PASSWORD);
		//2.����statement���������ִ��SQL���
		Statement stmt = null;
		ResultSet rs = null;
		stmt = conn.createStatement();
		Statement stmt1 = null;
		ResultSet rs1 = null;
		stmt1 = conn.createStatement();
		Statement stmt2 = null;
//		ResultSet rs2 = null;
		stmt2 = conn.createStatement();
		String sqlstr = null;
		if(controlerNum=="ALL")
		{
			sqlstr="select distinct controlerNum from gatesetting WHERE controlerNum != 'ALL' ORDER BY lasttime DESC;";
			rs = stmt.executeQuery(sqlstr);
			
			while(rs.next())
			{
				sqlstr="select MAX(skid) from staffbasesetting WHERE controlerNum='"+rs.getString(1)+"' AND staffnum='"+staffnum+"';";
				rs1 = stmt1.executeQuery(sqlstr);
				while(rs1.next())
				{
					if(rs1.getInt(1) != 0)
					{
						sqlstr="UPDATE staffbasesetting SET staffname='"+staffname+"',cardnum='"+cardnum+
							"',departmentname='"+departmentname+"',gatenum='"+gatenum+"',periodofvalitystart='"+
							periodofvalitystart+"',periodofvalityend='"+periodofvalityend+"',entrancevalitystart='"+
							entrancevalitystart+"',entrancevalityend='"+entrancevalityend+"',limitenrancenum='"+limitenrancenum+
							"',leavevalitystart='"+leavevalitystart+"',leavevalityend='"+leavevalityend+
							"',limitleavenum='"+limitleavenum+"',staffgender='"+staffgender+"',staffage="+
							staffage+",picfilename='"+picfilename+"' WHERE controlerNum='"+rs.getString(1)+
							"' AND staffnum='"+staffnum+"';"; 					
						int udresult  = stmt2.executeUpdate(sqlstr);
						if(udresult==1)
						{
							response.setContentType("text/html;charset=utf-8");
							response.setCharacterEncoding("UTF-8");
							PrintWriter out = response.getWriter();
							out.println("<script language=\"JavaScript\">alert('更新成功。');</script><br/>");
//							out.println("<script language=\"JavaScript\">self.setTimeout(window.location.href='/dw/door/staffbasesetting.jsp',5000);</script><br/>");
							out.println("<script language=\"JavaScript\">parent.setTimeout(parent.location.href='/dw/door/staffbasesetting.jsp',5000);</script><br/>");
							out.flush();
							out.close();
						}else
						{
//							response.setContentType("text/html");
							response.setContentType("text/html;charset=utf-8");
							response.setCharacterEncoding("UTF-8");
							PrintWriter out = response.getWriter();
							out.println("<script language=\"JavaScript\">alert('更新失败。');</script><br/>");
//							out.println("<script language=\"JavaScript\">self.setTimeout(window.location.href='/door/staffbasesetting.php',5000);</script><br/>");
							out.flush();
							out.close();
						}
					}else
					{
						sqlstr="insert into  staffbasesetting (staffnum,staffname,cardnum,departmentname," +
							"controlerNum,gatenum,periodofvalitystart,periodofvalityend,entrancevalitystart," +
							"entrancevalityend,limitenrancenum,leavevalitystart,leavevalityend,limitleavenum," +
							"staffgender,staffage,picfilename) values ('"+staffnum+"','"+staffname+"','"+
							cardnum+"','"+departmentname+"','"+rs.getString(1)+"','"+gatenum+"','"+periodofvalitystart+
							"','"+periodofvalityend+"','"+entrancevalitystart+"','"+entrancevalityend+"','"+
							limitenrancenum+"','"+leavevalitystart+"','"+leavevalityend+"','"+limitleavenum+
							"','"+staffgender+"',"+staffage+",'"+picfilename+"');"; 					
						int udresult  = stmt2.executeUpdate(sqlstr);
						if(udresult==1)
						{
//							response.setContentType("text/html");
							response.setContentType("text/html;charset=utf-8");
							response.setCharacterEncoding("UTF-8");
							PrintWriter out = response.getWriter();
							out.println("<script language=\"JavaScript\">alert('增加成功。');</script><br/>");
//							out.println("<script language=\"JavaScript\">self.setTimeout(window.location.href='/dw/door/staffbasesetting.jsp',5000);</script><br/>");
							out.println("<script language=\"JavaScript\">parent.setTimeout(parent.location.href='/dw/door/staffbasesetting.jsp',5000);</script><br/>");
							out.flush();
							out.close();
						}else
						{
//							response.setContentType("text/html");
							response.setContentType("text/html;charset=utf-8");
							response.setCharacterEncoding("UTF-8");
							PrintWriter out = response.getWriter();
							out.println("<script language=\"JavaScript\">alert('增加失败。');</script><br/>");
//							out.println("<script language=\"JavaScript\">self.setTimeout(window.location.href='/door/staffbasesetting.php',5000);</script><br/>");
							out.flush();
							out.close();
						}
					}
				}
			}
		
		}else
		{
			sqlstr="select MAX(skid) from staffbasesetting WHERE controlerNum='"+controlerNum+"' AND staffnum='"+staffnum+"';";
			rs1 = stmt1.executeQuery(sqlstr);
			while(rs1.next())
			{
				if(rs1.getInt(1) != 0)
				{
					sqlstr="UPDATE staffbasesetting SET staffname='"+staffname+"',cardnum='"+cardnum+
							"',departmentname='"+departmentname+"',gatenum='"+gatenum+"',periodofvalitystart='"+
							periodofvalitystart+"',periodofvalityend='"+periodofvalityend+"',entrancevalitystart='"+
							entrancevalitystart+"',entrancevalityend='"+entrancevalityend+"',limitenrancenum='"+limitenrancenum+
							"',leavevalitystart='"+leavevalitystart+"',leavevalityend='"+leavevalityend+
							"',limitleavenum='"+limitleavenum+"',staffgender='"+staffgender+"',staffage="+
							staffage+",picfilename='"+picfilename+"' WHERE controlerNum='"+controlerNum+
							"' AND staffnum='"+staffnum+"';"; 					
					int udresult  = stmt2.executeUpdate(sqlstr);
					System.out.println("sqlstr:"+sqlstr);
					if(udresult==1)
					{
//						response.setContentType("text/html");
						response.setContentType("text/html;charset=utf-8");
						response.setCharacterEncoding("UTF-8");
						PrintWriter out = response.getWriter();
//						String returnstr = "<script language=\"JavaScript\">alert('更新成功。');</script><br/>";
//						out.println(URLEncoder.encode(returnstr,"UTF-8"));
//						out.println(returnstr.getBytes("UTF-8"));
//						out.println("<script language=\"JavaScript\">alert('更新成功。');</script><br/>");
						out.println("<script language=\"JavaScript\">alert('更新成功。');</script><br/>");
//						out.println("<script language=\"JavaScript\">self.setTimeout(window.location.href='/dw/door/staffbasesetting.jsp',5000);</script><br/>");
						out.println("<script language=\"JavaScript\">parent.setTimeout(parent.location.href='/dw/door/staffbasesetting.jsp',5000);</script><br/>");
						out.flush();
						out.close();
					}else
					{
//						response.setContentType("text/html");
						response.setContentType("text/html;charset=utf-8");
						response.setCharacterEncoding("UTF-8");
						PrintWriter out = response.getWriter();
						out.println("<script language=\"JavaScript\">alert('更新失败。');</script><br/>");
//						out.println("<script language=\"JavaScript\">self.setTimeout(window.location.href='/door/staffbasesetting.php',5000);</script><br/>");
						out.flush();
						out.close();
					}
				}else
				{
					sqlstr="insert into  staffbasesetting (staffnum,staffname,cardnum,departmentname," +
							"controlerNum,gatenum,periodofvalitystart,periodofvalityend,entrancevalitystart," +
							"entrancevalityend,limitenrancenum,leavevalitystart,leavevalityend,limitleavenum," +
							"staffgender,staffage,picfilename) values ('"+staffnum+"','"+staffname+"','"+
							cardnum+"','"+departmentname+"','"+controlerNum+"','"+gatenum+"','"+periodofvalitystart+
							"','"+periodofvalityend+"','"+entrancevalitystart+"','"+entrancevalityend+"','"+
							limitenrancenum+"','"+leavevalitystart+"','"+leavevalityend+"','"+limitleavenum+
							"','"+staffgender+"',"+staffage+",'"+picfilename+"');"; 					
					int udresult  = stmt2.executeUpdate(sqlstr);
					System.out.println("sqlstr:"+sqlstr);
					if(udresult==1)
					{
//						response.setContentType("text/html");
						response.setContentType("text/html;charset=utf-8");
						response.setCharacterEncoding("UTF-8");
						PrintWriter out = response.getWriter();
						out.println("<script language=\"JavaScript\">alert('增加成功。');</script><br/>");
//						String returnstr = "<script language=\"JavaScript\">alert('更新成功。');</script><br/>";
//						out.println(returnstr.getBytes("UTF-8"));
//						out.println("<script language=\"JavaScript\">self.setTimeout(window.location.href='/dw/door/staffbasesetting.jsp',5000);</script><br/>");
						out.println("<script language=\"JavaScript\">parent.setTimeout(parent.location.href='/dw/door/staffbasesetting.jsp',5000);</script><br/>");
						out.flush();
						out.close();
					}else
					{
//						response.setContentType("text/html");
						response.setContentType("text/html;charset=utf-8");
						response.setCharacterEncoding("UTF-8");
						PrintWriter out = response.getWriter();
						out.println("<script language=\"JavaScript\">alert('增加失败。');</script><br/>");
//							out.println("<script language=\"JavaScript\">self.setTimeout(window.location.href='/door/staffbasesetting.php',5000);</script><br/>");
						out.flush();
						out.close();
					}
				}
			}
		}
		if(rs!=null) rs.close();
		if(stmt != null) stmt.close();
		if(rs1!=null) rs1.close();
		if(stmt1 != null) stmt1.close();
		if(stmt2 != null) stmt2.close();
		if(conn!=null) conn.close();
	}
	
	private void dwgetControlerNum(HttpServletResponse response) throws SQLException, IOException
	{
		try 
		{
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) 
		{
			System.out.println("com.mysql.jdbc.Driver Exception,109");
		}
		//����Connection����
		Connection conn = null;
//		try 
//		{
		//1.getConnection()����������MySQL��ݿ�
		conn = DriverManager.getConnection(URL, NAME, PASSWORD);
//		} catch (SQLException e) 
//		{
			// TODO Auto-generated catch block
	//					e.printStackTrace();
//			System.out.println("DriverManager.getConnection Exception,120");
//		}
		//2.����statement���������ִ��SQL���
		Statement stmt = null;
//		try 
//		{
		stmt = conn.createStatement();
//		} catch (SQLException e) 
//		{
			// TODO Auto-generated catch block
	//					e.printStackTrace();
//			System.out.println("conn.createStatement Exception,132");
//		}
		//3.ResultSet�࣬������Ż�ȡ�Ľ��
		ResultSet rs = null;
		String content = null;
//		try 
//		{
		content="select distinct controlerNum from gatesetting ORDER BY lasttime DESC;";
		rs = stmt.executeQuery(content);
		String controlertab = null;
		int i=1;
		while(rs.next())
		{									   
			if(i==1)
			{			        
				controlertab = "{\"s"+i+"_0\":\""+rs.getString(1)+"\"";
			}else
			{
				controlertab +=",\"s"+i+"_0\":\""+rs.getString(1)+"\"";
			}
			i++;						
		}
		controlertab += ",\"nrows\":\""+(i-1)+"\"}";
		rs.close();
		if(stmt!=null) stmt.close();
        if(conn!=null) conn.close();
		response.setContentType("text/html");
		PrintWriter out = response.getWriter();
		out.println(controlertab);
		out.flush();
		out.close();
//		System.out.println("controlertab��"+controlertab);
	}
	
	private void dwgetstaff(HttpServletResponse response,int staffpagenum) throws SQLException, IOException
	{
		try 
		{
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) 
		{
			System.out.println("com.mysql.jdbc.Driver Exception,109");
		}
		//����Connection����
		Connection conn = null;
//		try 
//		{
		//1.getConnection()����������MySQL��ݿ�
		conn = DriverManager.getConnection(URL, NAME, PASSWORD);
//		} catch (SQLException e) 
//		{
			// TODO Auto-generated catch block
	//					e.printStackTrace();
//			System.out.println("DriverManager.getConnection Exception,120");
//		}
		//2.����statement���������ִ��SQL���
		Statement stmt = null;
		Statement stmt1 = null;
//		try 
//		{
		stmt = conn.createStatement();
//		} catch (SQLException e) 
//		{
			// TODO Auto-generated catch block
	//					e.printStackTrace();
//			System.out.println("conn.createStatement Exception,132");
//		}
		//3.ResultSet�࣬������Ż�ȡ�Ľ��
		ResultSet rs = null;
		ResultSet rs1 = null;
		String content = null;
//		try 
//		{
		content="select COUNT(*) from staffbasesetting;";
		rs = stmt.executeQuery(content);
	
//		} catch (SQLException e) 
//		{
			// TODO Auto-generated catch block
	//		e.printStackTrace();
//			System.out.println("stmt.executeQuery Exception,140");
//		}
//		try 
//		{
		String stafftab = null;
		while(rs.next())
		{
			int count = rs.getInt(1);
/*    		        if($rowsnum[0]==0)
		    {
		        $blank = true;
		    }*/
//			System.out.println("count_154:"+count);
			
			if(count==0)
			{
				stafftab = "{\"nrows\":\"0\",\"ncols\":\"9\",\"pageMaxium\":\"0\",\"controlerpagenum\":\"0\"}";
			}else
			{
				int pagemax=1;
				pagemax = ((count/10.0)-(int)(count/10.0))>0.?(int)(count/10.0)+1:(int)(count/10.0);
				if(pagemax<1) pagemax=1;
				if(staffpagenum>pagemax) staffpagenum=pagemax;
				int pagestart=10*staffpagenum-10;
				int pageend=10;

				 //�������ĵ�ǰҳ�����һҳ���򷵻ص���Ŀ��ȡ�������̶�Ϊ10��
				if(staffpagenum == pagemax)
				{
					count = count%10;
					if(count == 0)
					{
						count = 10;
					}
				}
				else
				{
					count = 10;
				}

//				System.out.println("pagestart_200:"+pagestart+"   pageend:"+pageend);
				
//					try 
//					{
				stmt1 = conn.createStatement();
//					} catch (SQLException e) 
//					{
					// TODO Auto-generated catch block
//									e.printStackTrace();
//						System.out.println("conn.createStatement Exception,209");
//					}
//					try 
//		    		{
    			content="select skid,staffnum,staffname,cardnum,departmentname,controlerNum,gatenum,periodofvalitystart,periodofvalityend from staffbasesetting limit "+pagestart+","+pageend+";";
//	    			System.out.println("content_204:"+content);
    			rs1 = stmt1.executeQuery(content);			
//		    		} catch (SQLException e) 
//		    		{
	    			// TODO Auto-generated catch block
//	    			e.printStackTrace();
//		    			System.out.println("stmt.executeQuery Exception,207");
//		    		}
				int i=1;
				int j=1;			
				while(rs1.next())
    			{
					for(j=1;j<=9;j++)
					{			   
						if(i==1 && j==1)
						{			        
							stafftab = "{\"nrows\":\""+count+"\",\"ncols\":\"10\",\"pageMaxium\":\""+pagemax+"\",\"controlerpagenum\":\""+staffpagenum+"\",\"s0"+i+"_"+(j-1)+"\":\""+rs1.getString(j)+"\"";						
						}else
						{
							stafftab += ",\"s0"+i+'_'+(j-1)+"\":\""+rs1.getString(j)+"\"";
						}
					}
					i++;
	    			}
				stafftab += "}";
				rs1.close();
			}
		}
		rs.close();
		sun.misc.BASE64Encoder encoder = new sun.misc.BASE64Encoder();
		//		System.out.println("accesstab,252"+accesstab);
		String en = encoder.encode(stafftab.getBytes("utf-8"));
		response.setContentType("text/html");
		PrintWriter out = response.getWriter();
		out.println(en);
		out.flush();
		out.close();
//		} catch (SQLException e) 
//		{
			// TODO Auto-generated catch block
//			e.printStackTrace();
//			System.out.println("rs.next Exception,261");
//		}
		// �ر���Դ
//	    try
//	    {
        if(stmt!=null) stmt.close();
        if(stmt1!=null) stmt1.close();
//	    }catch(SQLException se2)
//	    {
//	    }// ʲô������
//	    try
//	    {
        if(conn!=null) conn.close();
//	    }catch(SQLException se)
//	    {
//	        se.printStackTrace();
//	    }
	}
	
	private void exceltable(HttpServletResponse response,String tablename) throws SQLException, IOException
	{
		try 
		{
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) 
		{
//			System.out.println("com.mysql.jdbc.Driver Exception,109");
		}
		//����Connection����
		Connection conn = null;

		//1.getConnection()����������MySQL��ݿ�
		conn = DriverManager.getConnection(URL, NAME, PASSWORD);

		//2.����statement���������ִ��SQL���
		Statement stmt = null;
		stmt = conn.createStatement();
		ResultSet rs = null;
		String content = null;

//		content="select * from "+tablename+";";
		content="select tkid,cardnum,cardlen,controlerNum,gatenum,CreatedTime,accessstat,entrance from "+tablename+";";
		rs = stmt.executeQuery(content);
//		System.out.println("mysql:"+content);
		String returnstr = "tkid,cardnum,cardlen,controlerNum,gatenum,CreatedTime,accessstat,entrance \n";
		while(rs.next())
		{
			String tmpstr = String.valueOf(rs.getInt(1));
			tmpstr = new String(tmpstr.getBytes("gbk"),"utf-8");
			returnstr += tmpstr+",";
//			System.out.println("returnstr:"+returnstr);
			int i=0;
			for(i=2;i<9;i++)
			{					
				tmpstr = rs.getString(i);
//				if(tmpstr.isEmpty()) tmpstr = "��";
				if(tmpstr==null) tmpstr = "��";
//				System.out.println("returnstr_1:"+returnstr);
				tmpstr = new String(tmpstr.getBytes("gbk"),"utf-8");
//				System.out.println("returnstr+2:"+returnstr);
				if(i==8)
				{
					returnstr += tmpstr+"\n";
				}else
				{
					returnstr += tmpstr+",";
				}
			}
		}
		rs.close();
		if(stmt!=null) stmt.close();
        if(conn!=null) conn.close();
		//		System.out.println("accesstab,252"+accesstab);
//        returnstr = new String(returnstr.getBytes("gbk"),"utf-8");
//        String rstr = "Content-Disposition:attachment;filename="+tablename+".csv";
		response.setContentType("Content-Type: application/vnd.ms-excel; charset=GB2312");
//        response.setContentType("Content-Type: application/vnd.ms-excel;");
//        response.setContentType("application/x-msdownload");
//        response.setContentType("application/x-msdownload; charset=GB2312");
		PrintWriter out = response.getWriter();
//		out.println("Content-Disposition:attachment;filename=");
//		out.print(tablename);
//		out.println(".csv");
//		out.println(rstr);
//		out.print(rstr);
//		response.setHeader("Content-Disposition:attachment","attachment;filename=" + new String( tablename.getBytes("gb2312"), "ISO8859-1" ));
//		response.setHeader("Content-Disposition:attachment","filename=" + new String( tablename.getBytes("gb2312"), "utf-8" ));
//		response.setHeader("Content-Disposition:attachment","filename=" + new String( tablename.getBytes("utf-8"), "ISO8859-1" ));
		response.setHeader("Content-Disposition:attachment","filename=" + tablename + ".csv");
		out.print(returnstr);
		out.flush();
		out.close();       
	}	
	
	private void dwviewaccessdetail(HttpServletResponse response) throws SQLException, UnsupportedEncodingException
	{
		try 
		{
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) 
		{
//			System.out.println("com.mysql.jdbc.Driver Exception,109");
		}
		//����Connection����
		Connection conn = null;

			//1.getConnection()����������MySQL��ݿ�
			conn = DriverManager.getConnection(URL, NAME, PASSWORD);

		//2.����statement���������ִ��SQL���
		Statement stmt = null;
		Statement stmt1 = null;
		Statement stmt2 = null;
		Statement stmt3 = null;
		Statement stmt4 = null;
		stmt = conn.createStatement();
		stmt1 = conn.createStatement();
		stmt2 = conn.createStatement();
		stmt3 = conn.createStatement();
		stmt4 = conn.createStatement();
		//3.ResultSet�࣬������Ż�ȡ�Ľ��
		ResultSet rs = null;
		ResultSet rs1 = null;
		ResultSet rs2 = null;
		ResultSet rs3 = null;
		ResultSet rs4 = null;
		
		String content = null;

		content="SELECT MAX(CreatedTime) FROM accessrecord;";
		rs = stmt.executeQuery(content);					
		rs.last();
		String CreatedTime = rs.getString(1);
		rs.close();
//		System.out.println("CreatedTime:"+CreatedTime);
		
		content="SELECT cardnum,controlerNum,gatenum,tkid FROM accessrecord WHERE CreatedTime = '"+
			CreatedTime+"';";
		rs1 = stmt1.executeQuery(content);
		rs1.last();		
//		System.out.println("cardnum:"+rs1.getString(1)+" controlerNum:"+rs1.getString(2)+" gatenum:"+
//			rs1.getString(3)+" tkid:"+rs1.getInt(4));
		
		content="select staffname,departmentname from staffbasesetting where cardnum='"+rs1.getString(1)+
			"'AND controlerNum='"+rs1.getString(2)+"' AND (gatenum='"+rs1.getString(3)+"' OR gatenum='all');";		
		rs2 = stmt2.executeQuery(content);
		String staffname = null;
		String departmentname = null;
		while(rs2.next())
		{			
			if(rs2.getString(1).isEmpty())
			{
				staffname = "��";
			}else
			{
				staffname = rs2.getString(1);
			}
			if(rs2.getString(2).isEmpty())
			{
				departmentname = "��";
			}else
			{
				departmentname = rs2.getString(2);
			}
		}
//		System.out.println("staffname:"+staffname+" departmentname:"+departmentname);
		
		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
//			ParsePosition pos = new ParsePosition(8);
//			Date CreatedTime_date = formatter.parse(CreatedTime, pos);
		DateFormat dateFormat;
		dateFormat = new SimpleDateFormat("yyyy-MM-dd kk:mm:ss", Locale.ENGLISH);
		dateFormat.setLenient(false);
		java.util.Date timeDate = null;
		String todaystart = null;
		String todayend = null;
		String todaytime = null;
		try 
		{
			timeDate = dateFormat.parse(CreatedTime);     //util����
			todaystart = formatter.format(timeDate)+" 00:00:00";
			todayend = formatter.format(timeDate)+" 23:59:59";
			todaytime = dateFormat.format(timeDate);
		} catch (ParseException e1)
		{
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
//		System.out.println("CreatedTime:"+todaytime+" CreatedTime_date: "+formatter.format(timeDate)+
//			" todaystart: "+todaystart+" todayend:"+todayend);
		
		content="SELECT COUNT(*) FROM accessrecord WHERE accessstat='pass' AND entrance='in' AND CreatedTime " +
			"BETWEEN '"+todaystart+"' AND '"+todayend+"';";				
		rs3 = stmt3.executeQuery(content);
		rs3.last();
		int todayin = rs3.getInt(1);
		
		
		content="SELECT COUNT(*) FROM accessrecord WHERE accessstat='pass' AND entrance='out' AND CreatedTime " +
			"BETWEEN '"+todaystart+"' AND '"+todayend+"';";
		rs4 = stmt4.executeQuery(content);
		rs4.last();
		int todayout = rs4.getInt(1);
		
		String accesstab = null;		
		accesstab = "{\"staffname\":\""+staffname+"\",\"departmentname\":\""+departmentname+
			"\",\"cardnum\":\""+rs1.getString(1)+"\",\"controlerNum\":\""+rs1.getString(2)+
			"\",\"gatenum\":\""+rs1.getString(3)+"\",\"CreatedTime\":\""+todaytime+"\",\"todayin\":\""+
			todayin+"\",\"todayout\":\""+todayout+"\",\"picfilename\":\""+rs1.getInt(4)+"\"}";
		
		rs1.close();
		rs2.close();
		rs3.close();
		rs4.close();
		
		sun.misc.BASE64Encoder encoder = new sun.misc.BASE64Encoder();	
//      byte[] en = null;
//      content="{\"res\":\"deny5\"}";
//		String en = encoder.encode(accesstab);
        String en = encoder.encode(accesstab.getBytes("utf-8"));
		
//		System.out.println("dwviewaccessdetail"+accesstab);
//		response.setContentType("text/html");
		response.setContentType("text/html;charset=utf-8");
		response.setCharacterEncoding("UTF-8");
		PrintWriter out = null;
		try 
		{
			out = response.getWriter();
		} catch (IOException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		out.println(en);
		out.flush();
		out.close();
		
		// �ر���Դ
        if(stmt!=null) stmt.close();
        if(stmt1!=null) stmt1.close();
        if(stmt2!=null) stmt2.close();
        if(stmt3!=null) stmt3.close();
        if(stmt4!=null) stmt4.close();
        if(conn!=null) conn.close();
	}
	
	private void dwgetcontroler(HttpServletResponse response,int controlerpagenum) throws SQLException, IOException
	{
		try 
		{
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) 
		{
			System.out.println("com.mysql.jdbc.Driver Exception,109");
		}
		//����Connection����
		Connection conn = null;
//		try 
//		{
			//1.getConnection()����������MySQL��ݿ�
			conn = DriverManager.getConnection(URL, NAME, PASSWORD);
//		} catch (SQLException e) 
//		{
			// TODO Auto-generated catch block
	//					e.printStackTrace();
//			System.out.println("DriverManager.getConnection Exception,120");
//		}
		//2.����statement���������ִ��SQL���
		Statement stmt = null;
		Statement stmt1 = null;
//		try 
//		{
			stmt = conn.createStatement();
//		} catch (SQLException e) 
//		{
			// TODO Auto-generated catch block
	//					e.printStackTrace();
//			System.out.println("conn.createStatement Exception,132");
//		}
		//3.ResultSet�࣬������Ż�ȡ�Ľ��
		ResultSet rs = null;
		ResultSet rs1 = null;
		String content = null;
//		try 
//		{
			content="select COUNT(*) from gatesetting WHERE controlerNum!='ALL';";
			rs = stmt.executeQuery(content);			
//		} catch (SQLException e) 
//		{
			// TODO Auto-generated catch block
	//		e.printStackTrace();
//			System.out.println("stmt.executeQuery Exception,140");
//		}
//		try 
//		{
			String accesstab = null;
			while(rs.next())
			{
				int count = rs.getInt(1);
				count = (count/4);
	/*    		        if($rowsnum[0]==0)
			    {
			        $blank = true;
			    }*/
//				System.out.println("count_154:"+count);
				if(count==0)
				{
					accesstab = "{\"nrows\":\"0\",\"ncols\":\"5\",\"pageMaxium\":\"0\",\"controlerpagenum\":\"0\"}";
	/*						Statement stmt1 = null;
					try 
					{
						stmt1 = conn.createStatement();
					} catch (SQLException e) 
					{
						// TODO Auto-generated catch block
	//									e.printStackTrace();
						System.out.println("conn.createStatement Exception,918");
					}
					content="UPDATE gatesetting SET stat= 'offline'  WHERE controlerNum ='"+controlerNum+"'";
	//					ResultSet rs1  = stmt.executeQuery(content);			
	//					rs1.close();
					udresult  = stmt1.executeUpdate(content);
					if(stmt1!=null) stmt1.close();*/
				}else
				{
					int pagemax=1;
//					int controlerpagenum=1;
					pagemax = ((count/5.0)-(int)(count/5.0))>0.?(int)(count/5.0)+1:(int)(count/5.0);
//					System.out.println("pagemax_177:"+pagemax);
					if(pagemax<1) pagemax=1;
	//    			$string = "pagemax=".$pagemax."\n";
	//			    file_put_contents("test.log", $string, FILE_APPEND);
					if(controlerpagenum>pagemax) controlerpagenum=pagemax;
					//�������ĵ�ǰҳ�����һҳ���򷵻ص���Ŀ��ȡ�������̶�Ϊ10��
					if(controlerpagenum == pagemax)
					{
						if(count%5==0)
						{
							count = 5;
						}else
						{
							count = count%5;
						}
					}
					else
					{
						count = 5;
					}
	
					int pagestart=5*controlerpagenum-5;
					int pageend=5;
	//				System.out.println("pagestart_200:"+pagestart+"   pageend:"+pageend);
					
//					try 
//					{
						stmt1 = conn.createStatement();
//					} catch (SQLException e) 
//					{
						// TODO Auto-generated catch block
	//									e.printStackTrace();
//						System.out.println("conn.createStatement Exception,209");
//					}
//					try 
//		    		{
		    			content="select DISTINCT controlerNum,IP,PORT,lasttime,stat from gatesetting WHERE controlerNum!='ALL' ORDER BY lasttime desc limit "+pagestart+","+pageend+";";
	//	    			System.out.println("content_204:"+content);
		    			rs1 = stmt1.executeQuery(content);			
//		    		} catch (SQLException e) 
//		    		{
		    			// TODO Auto-generated catch block
	//	    			e.printStackTrace();
//		    			System.out.println("stmt.executeQuery Exception,207");
//		    		}
					int i=1;
					int j=1;			
					while(rs1.next())
	    			{
	/*    						int tkid = rs1.getInt(1);
						String controlerNum = rs1.getString(1);
						String IP = rs1.getString(2);
						String PORT = rs1.getString(3);
						String lasttime = rs1.getString(4);
						String stat = rs1.getString(5);
	//					String entrance = rs1.getString(7);
	//					System.out.println("controlerNum:"+controlerNum+" IP: "+IP+" PORT: "+PORT+
	//							" lasttime: "+lasttime+" stat: "+stat);*/
						for(j=1;j<=5;j++)
						{			   
							if(i==1 && j==1)
							{			        
								accesstab = "{\"nrows\":\""+count+"\",\"ncols\":\"5\",\"pageMaxium\":\""+pagemax+"\",\"controlerpagenum\":\""+controlerpagenum+"\",\"s0"+i+"_"+(j-1)+"\":\""+rs1.getString(j)+"\"";						
							}else
							{
								accesstab += ",\"s0"+i+'_'+(j-1)+"\":\""+rs1.getString(j)+"\"";
							}
						}
						i++;
		    			}
				    accesstab += "}";
					rs1.close();
				}
			}
			rs.close();
	//		System.out.println("accesstab,252"+accesstab);
			response.setContentType("text/html");
			PrintWriter out = response.getWriter();
			out.println(accesstab);
			out.flush();
			out.close();
//		} catch (SQLException e) 
//		{
			// TODO Auto-generated catch block
//			e.printStackTrace();
//			System.out.println("rs.next Exception,261");
//		}
		// �ر���Դ
//	    try
//	    {
	        if(stmt!=null) stmt.close();
	        if(stmt1!=null) stmt1.close();
//	    }catch(SQLException se2)
//	    {
//	    }// ʲô������
//	    try
//	    {
	        if(conn!=null) conn.close();
//	    }catch(SQLException se)
//	    {
//	        se.printStackTrace();
//	    }
	}

	private void dwaccessrecord(HttpServletResponse response,int controlerpagenum) throws SQLException, IOException
	{
		try 
		{
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) 
		{
	//		System.out.println("com.mysql.jdbc.Driver Exception,109");
		}
		//����Connection����
		Connection conn = null;

			//1.getConnection()����������MySQL��ݿ�
			conn = DriverManager.getConnection(URL, NAME, PASSWORD);

		//2.����statement���������ִ��SQL���
		Statement stmt = null;
		Statement stmt1 = null;

			stmt = conn.createStatement();

		//3.ResultSet�࣬������Ż�ȡ�Ľ��
		ResultSet rs = null;
		ResultSet rs1 = null;
		String content = null;

			content="select COUNT(*) from accessrecord;";
			rs = stmt.executeQuery(content);			

			String accesstab = null;
			while(rs.next())
			{
				int count = rs.getInt(1);
//				System.out.println("count_154:"+count);
				if(count==0)
				{
					accesstab = "{\"nrows\":\"0\",\"ncols\":\"7\",\"pageMaxium\":\"0\",\"controlerpagenum\":\"0\"}";
	/*						Statement stmt1 = null;
					try 
					{
						stmt1 = conn.createStatement();
					} catch (SQLException e) 
					{
						// TODO Auto-generated catch block
	//									e.printStackTrace();
						System.out.println("conn.createStatement Exception,918");
					}
					content="UPDATE gatesetting SET stat= 'offline'  WHERE controlerNum ='"+controlerNum+"'";
	//					ResultSet rs1  = stmt.executeQuery(content);			
	//					rs1.close();
					udresult  = stmt1.executeUpdate(content);
					if(stmt1!=null) stmt1.close();*/
				}else
				{
					int pagemax=1;
//					int controlerpagenum=1;
					pagemax = ((count/10.0)-(int)(count/10.0))>0.?(int)(count/10.0)+1:(int)(count/10.0);
//					System.out.println("pagemax_177:"+pagemax);
					if(pagemax<1) pagemax=1;
	//    			$string = "pagemax=".$pagemax."\n";
	//			    file_put_contents("test.log", $string, FILE_APPEND);
					if(controlerpagenum>pagemax) controlerpagenum=pagemax;
					//�������ĵ�ǰҳ�����һҳ���򷵻ص���Ŀ��ȡ�������̶�Ϊ10��
	/*    					if($controlerpagenum == $pagemax)
					{
						if(($rowsnum[0]%10)==0)
						{
							$rowsnum[0] = 10;
						}else
						{
							$rowsnum[0] = $rowsnum[0]%10;
						}
					}
					else
					{
						$rowsnum[0] = 10;
					}*/
	
					int pagestart=10*controlerpagenum-10;
					int pageend=10;
	//				System.out.println("pagestart_200:"+pagestart+"   pageend:"+pageend);
					if(controlerpagenum == pagemax)
					{
						if((count%10)==0)
						{
							count = 10;
						}else
						{
							count = count%10;
						}
					}
					else
					{
						count = 10;
					}

						stmt1 = conn.createStatement();

		    			content="select tkid,cardnum,gatenum,controlerNum,CreatedTime,accessstat,entrance from accessrecord ORDER BY CreatedTime DESC limit "+pagestart+","+pageend+";";
	//	    			System.out.println("content_396:"+content);
		    			rs1 = stmt1.executeQuery(content);			

					int i=1;
					int j=1;			
					while(rs1.next())
	    			{
	/*    						int tkid = rs1.getInt(1);
						String cardnum = rs1.getString(2);
						String gatenum = rs1.getString(3);
						String controlerNum = rs1.getString(4);
						String CreatedTime = rs1.getString(5);
						String accessstat = rs1.getString(6);
						String entrance = rs1.getString(7);
						System.out.println("tkid:"+tkid+" cardnum: "+cardnum+" gatenum: "+gatenum+
								" controlerNum: "+controlerNum+" CreatedTime: "+CreatedTime+
								" accessstat: "+accessstat+" entrance: "+entrance);*/
						for(j=1;j<=7;j++)
						{			   
							if(i==1 && j==1)
							{			        
								accesstab = "{\"nrows\":\""+count+"\",\"ncols\":\"7\",\"pageMaxium\":\""+pagemax+"\",\"controlerpagenum\":\""+controlerpagenum+"\",\"s0"+i+"_"+(j-1)+"\":\""+rs1.getInt(j)+"\"";						
							}else if(j==1)
							{
								accesstab += ",\"s0"+i+'_'+(j-1)+"\":\""+rs1.getInt(j)+"\"";
							}else
							{
								accesstab += ",\"s0"+i+'_'+(j-1)+"\":\""+rs1.getString(j)+"\"";
							}
						}
						i++;
		    			}
				    accesstab += "}";
					rs1.close();
				}
			}
			rs.close();
	//		System.out.println("accesstab,438"+accesstab);
			response.setContentType("text/html");
			PrintWriter out = response.getWriter();
			out.println(accesstab);
			out.flush();
			out.close();
		// �ر���Դ

	        if(stmt!=null) stmt.close();
	        if(stmt1!=null) stmt1.close();

	        if(conn!=null) conn.close();

	}
	
	/**
	 * Initialization of the servlet. <br>
	 *
	 * @throws ServletException if an error occurs
	 */
	public void init() throws ServletException 
	{
		// Put your code here
	}
}
